Friday, March 11, 2011

Shrink SQL Server Log File

Below are simple steps to shrink transaction log files in SQLSERVER.

sp_helpdb 'DATABASE_NAME'

Truncate the log by changing the database recovery model to SIMPLE

USE DATABASE_NAME;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE DATABASE_NAME
SET RECOVERY SIMPLE;
GO

Shrink the truncated log file to 1 MB:

DBCC SHRINKFILE (LOF_FILE_NAME, 1);

Reset the database recovery model.

GO
-- Reset the database recovery model.
ALTER DATABASE DATABASE_NAME
SET RECOVERY FULL;
GO

No comments: