Wednesday, March 11, 2009

Truncate MS SQL transaction Log file for SharePoint 2007 DB

One of the most painful issues to the SharePoint administrator is truncating the transaction log file for sharepoint DB.
If you tried to do shrinking thru:

1-RightClick on DB --> tasks -->Shrink -->Files

Result: NO CHANGES

2- Using T-SQL command:
DBCC SHRINKFILE('WSS_Content_WebApp_log',50);

Result: Error Message: Cannot shrink log file 2 (WSS_Content_WebApp_log) because all logical log files are in use.

Now the steps to avoid popular problems u faced doing this task.

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

NOTE: Don't leave ur db Recovery in SIMPLE mode...this will cause some problems when u try to delete site collection from sharepoint central admin.
ALWAYS keep it in FULL mode.