Thursday, October 11, 2007

Lose a bloaty Transaction Log file

Shrinking Log files
-- find logical name of log file
SELECT * FROM sys.sysfiles

-- Change recovery model to SIMPLE.
ALTER DATABASE DATABASE_NAME SET RECOVERY SIMPLE

-- Shrink truncated log file to 1 MB.
DBCC SHRINKFILE (data_log, 1);
GO

-- Change recovery model to FULL.
ALTER DATABASE DATABASE_NAME SET RECOVERY FULL

!!! Take FULL backup as your chain of Transaction log backups will now be broken by the RECOVERY mode change.

No comments: