Shrinking a large transaction log on a full drive
- by Sam
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?
