Founder's blog

How To Shrink Log on MS SQL 2008

Dec 31 2010 :: by Alex
SQL Server 2008 does not support the "backup log with truncate_only" option any longer. So if you want to shrink a database transaction log on an SQL 2008 server, use this script:

USE MyDB

ALTER DATABASE MyDB SET Recovery simple
GO

checkpoint
GO

DECLARE @LogFileLogicalName sysname
SELECT @LogFileLogicalName=Name FROM sys.database_files WHERE TYPE=1

DBCC Shrinkfile(@LogFileLogicalName,1)
GO

ALTER DATABASE MyDB SET Recovery FULL



There are surprisingly few articles on this on the Internet, so enjoy. And remember to do a full database backup right after shrinking the log file! Cause you won't be able to restore "to a point in time" once the log is truncated.

'How To Shrink Log on MS SQL 2008' was written by Alex by Alex. CEO, founder


comments