Tuesday, 13 January 2009

Shrink SQL Server Log File LDF

Having recently had a very intensive database with limited data generating a huge transaction log file (>4Gb for a 150Mb database) it was necessary to carry out a temporary truncate and then schedule once per day, the following prodecure is very effective:

USE {{DatabaseName}}
GO

DBCC SHRINKFILE({{FileName}}, 1)
GO

BACKUP LOG WITH TRUNCATE_ONLY
GO

DBCC SHRINKFILE({{FileName}}, 1)
GO

No comments: