Below is DBCC SHRINKFILE statement to shrink the transaction log file manually :

For SQL Server 2005 :

USE [DatabaseName]
GO
DBCC SHRINKFILE([DatabaseName_log], 1)
BACKUP LOG [DatabaseName] WITH TRUNCATE_ONLY
DBCC SHRINKFILE([DatabaseName_log], 1)
GO

For SQL Server 2008 :

USE [DatabaseName]
GO
--Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE [DatabaseName]
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (DatabaseName_Log, 1);
GO
--Reset the database recovery model.
ALTER DATABASE [DatabaseName]
SET RECOVERY FULL;
GO

Leave a comment

comments