Tags: attach, database, detaching, files, intended, log, logs, microsoft, multiple, mysql, oracle, server, sql, transaction

Attach DB Without Transaction Logs

On Database » Microsoft SQL Server

2,630 words with 5 Comments; publish: Wed, 02 Jan 2008 02:58:00 GMT; (25046.88, « »)

Hi, there,

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?

Thank you.

Regards,

Yong Hwee

All Comments

Leave a comment...

  • 5 Comments
    • Hi, Tosc,

      Thank you for your immediate reply. It seems like there is no workaround for database with multiple log files.

      Regards,

      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

      Refer : http://madhuottapalam.blogspot.com/search?q=Transaction+Log

      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
    • Hi, there,

      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.

      Thank you.

      Regards,

      Yong Hwee

      #5; Thu, 13 Sep 2007 00:40:00 GMT