Sunday, 15 March 2009

Shrink a transaction log without doing a backup

If you use Microsoft SQL 2005 (or 2000, and probably 2008) then Sometimes you just have to shrink the transaction log in a hurry especially if you get this error:-

The transaction log for database 'DB NAME' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

I can never remember the command to truncate the log before you can use the inbuilt tools to shrink it.


In case you have the same problem here is the command:


BACKUP LOG [DBNAME] WITH Truncate_only


you should then be able to shrink the log without issue


P.S. There are reasons why you wouldn't want to do this (mainly around data recovery) so only do this on a test/dev environment.


Thanks


Ross

No comments: