none
Attach DB Without Transaction Logs

    Question

  • 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

    Monday, April 16, 2007 8:54 AM

Answers

  • 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

    Wednesday, April 18, 2007 12:03 AM

All replies

  • Hi,

     

    you can use sp_attach_single_file_db  but it works only on databases that have a single log file.

    When sp_attach_single_file_db attaches the database to the server, it builds a new log file. If the database is read-only, the log file is built in its previous location.

    ...

     OR by reading this thread attaching DB without .ldf file ???

     

    CU

    tosc

    Monday, April 16, 2007 3:20 PM
  • Hi, Tosc,

     

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

     

    Regards,

    Yong Hwee

    Tuesday, April 17, 2007 5:42 AM
  • 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

     

    Tuesday, April 17, 2007 9:00 AM
    Moderator
  • 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.

     

    • Proposed as answer by gnairooze Wednesday, August 03, 2011 6:13 PM
    Tuesday, April 17, 2007 5:09 PM
  • 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

    Wednesday, April 18, 2007 12:03 AM