Cannot shrink transaction log SQL 2005
If you ever come across a MS SQL 2005 transaction log that refuses to shrink, this little bit of code might help you out.
Shrinking transaction log
I tried all the usual ways of shrinking the files – right click shrink DB, shrink Logs etc., manually backup the logs twice etc. but this one log refused to shrink and it was filling the drive that was shared by several other databases.
I happened upon this code which solved the problem quickly and without any fuss. Needless to say you should backup your db and your transaction log before doing this:
-- Shrink the Transaction Log USE DatabaseName GO DBCC SHRINKFILE(TransactionLogName, 100) GO
A few things to note:
- The number ‘100’ in the DBCC line is the size in MB that you want to set the transaction log.
- The TransactionLogName is the name of the file without it’s .LDF extension.
If you want to be even more thorough try the following:
-- Shrink the Transaction Log USE DatabaseName GO DBCC SHRINKFILE(, 100) BACKUP LOG WITH TRUNCATE_ONLY DBCC SHRINKFILE(TransactionLogName, 100) GO
Posted on July 28, 2012 at 20:12 by simon · Permalink
In: SQL · Tagged with: dbcc, sql, Transaction log
In: SQL · Tagged with: dbcc, sql, Transaction log