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.

5 comments:

Anonymous said...

This was brilliant and on target. I use Avepoint for backup and the ContentDB transaction log had grown too much.

Alexis said...
This comment has been removed by the author.
Alexis said...

For work with sql files I usually use-mssql recovery.Because tool is quite reliable and has free status as far as I remember.Moreover tool helped me many times and can also repair data from corrupted databases in the MS SQL Server format (files with the *.mdf extension).

Alex said...

Today I was in the Internet and saw there an interesting tool - sql database recovery tools. I downloaded it and the software resolved all my old problems with sql server. Moreover I knew how the tool recovers data from corrupted databases in MS SQL Server format.

dotNetFollower said...

Hello!
Here is the almost same way to shrink sharepoint database transaction log.
Thanks!