Truncate MS SQL transaction Log file

Most users always suggest to detach and re-attach DB to remove the log file when dealing with large Transaction log file. There is one proper way to truncate the transaction log file to certain size. Here are the sample steps to truncate SQL Log file:

USE AdventureWorks;
GO
– Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE AdventureWorks
SET RECOVERY SIMPLE;
GO
– Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (AdventureWorks_Log, 1);
GO
– Reset the database recovery model.
ALTER DATABASE AdventureWorks
SET RECOVERY FULL;
GO

You can find the details here: http://msdn.microsoft.com/en-us/library/ms189493.aspx

Leave a Reply