Tuesday, August 4, 2009

SQL Server - Shrinking LDF files

No rocket science in this post, I'm more putting these commands on here in case I ever lose them!

Log (LDF) files are obviously very important on a SQL Server and shouldn't just be wiped out every time you need some disk space - most of the time its not worth forcibly shrinking them since they'll just grow back to their old size. Its better to actually backup the transaction logs so SQL Server reuses the space within them and leave yourself the option of a point-in-time restore.

Saying that, every so often you do just know they only contain junk and won't ever be that big again - say with an archive database that's just been imported and will never change, and sometime in a disk space crisis they're the only option, so these commands will safely discard them and take them back to about 1mb. Be warned though, you've just nailed all your logs, its then best to immediately do a full backup on said database. Replace DATABASE and DATABASE_log with your actual DB name and its logs name (look in its properties through Management Studio).

First execute:

BACKUP LOG "DATABASE" WITH TRUNCATE_ONLY

Then execute:

USE "DATABASE"
GO
DBCC SHRINKFILE("DATABASE_Log", 2)
GO

And as if by magic, the logs are gone. Now backup, backup, backup...

No comments:

Post a Comment