Truncate MS SQL transaction Log file
October 7th, 2008 — ¥ong¥s
Online project management, task software, and free collaboration workspace: AJAXWorkspace
AJAXWorkspace is a free online workspace that offers task tracking software, team collaboration, online calendar, document sharing, and file management for project management and teamwork. |
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