Tuesday, 26 February 2008

Shrinking Log files in SQL Server 2005

So you, like I, have a database in SQL Server 2005 who's log file (.ldf) has grown massive.

Here is a quick but of SQL that should help you shrink the file to reclaim some disk space. be warned, this might impeed your ability to restore the database later if it all goes wibble.

First run the following

USE nameOfTheDatabaseHere
SELECT * FROM sysfiles

Now, take note of the fileid of the log file (usualy 2 for most databases) then do

( fileIDGoesHere, TRUNCATEONLY )

This will truncate the log file to as small as the DB Server will allow.

See http://msdn2.microsoft.com/en-us/library/aa258824.aspx for more info

No comments:

Post a Comment