Tags: attach, database, detaching, files, intended, log, logs, microsoft, multiple, mysql, oracle, server, sql, transaction
Attach DB Without Transaction Logs
I have multiple transaction log files. I don't need the transaction logs and intended to remove them. I have tried detaching the database, rename the logs and trying to re-attach the database without the logs but to no avail. The error message mentioned something like file activation failed and SQL couldn't rebuild the transaction logs.
Is there a way to get rid of the transaction log files?
Leave a comment...
- 5 Comments
you can use by reading this thread attaching DB without .ldf file ?
tosc#1; Thu, 13 Sep 2007 00:36:00 GMT
Thank you for your immediate reply. It seems like there is no workaround for database with multiple log files.
Yong Hwee#2; Thu, 13 Sep 2007 00:37:00 GMT
There are lot of limitation with sp_attach_single_file_db. The database should not have morethan one LOG and Datafile and the database should be properly detached. So in your case this process will not do. You requirement is to remove the Secondary LOG File, if am correct. What you can do is, Remove the secondary log file first using
DBCC SHRINKFILE (,EMPTYFILE)
alter database test remove file .
Then you can trucate the primary log file to any size you want. Please take backup of the database before starting anything
Madhu#3; Thu, 13 Sep 2007 00:38:00 GMT
In SQL Server 2005 you can you use CREATE DATABASE syntax to rebuild the log. The ATTACH_REBUILD_LOG syntax should work even if there are multiple log files.#4; Thu, 13 Sep 2007 00:39:00 GMT
Thank you for your reply. I managed to delete the log by first backup with option to truncate log after which I change the database to simple recovery. I can delete the logs this way.
Yong Hwee#5; Thu, 13 Sep 2007 00:40:00 GMT