Home / server / Questions / 121737
Accepted
Sam
Asked: 2010-03-12 16:06:26 +0800 CST

Shrinking a large transaction log on a full drive

  • 772

Someone fired off an update statement as part of some maintenance which did a cross join update on two tables with 200,000 records in each. That's 40 trillion statements, which would explain part of how the log grew to 200GB. I also did not have the log file capped, which is another problem I will be taking care of server wide - where we have almost 200 databases residing.

The 'solution' I used was to backup the database, backup the log with truncate_only, and then backup the database again. I then shrunk the log file and set a cap on the log.

Seeing as there were other databases using the log drive, I was in a bit of a rush to clean it out. I might have been able to back the log file up to our backup drive, hoping that no other databases needed to grow their log file.

Paul Randal from http://technet.microsoft.com/en-us/magazine/2009.02.logging.aspx

Under no circumstances should you delete the transaction log, try to rebuild it using undocumented commands, or simply truncate it using the NO_LOG or TRUNCATE_ONLY options of BACKUP LOG (which have been removed in SQL Server 2008). These options will either cause transactional inconsistency (and more than likely corruption) or remove the possibility of being able to properly recover the database.

Were there any other options I'm not aware of?

sql-server

1 Answers

  1. Best Answer

    You could've put the database into Simple recovery model (then use the CHECKPOINT command to make sure that the log is as truncated as possible) and then back into the Full recovery model. Then take a full database backup so that the system realises that it's in Full.

    Then you can shrink the log at your convenience. Shrinking a log isn't nearly as awful as shrinking the database, which you should almost never do.

    You haven't deleted the log, you've just truncated it - which kills your option to restore to a point in time. So do a full backup of your database as soon as possible.

    Also bear in mind that if your log can't grow, your database will stop. So leaving autogrow on isn't a bad option at all... but perhaps set it up to send you an alert when it's filling up.

    • 2