Wednesday, June 20, 2012

Shrink SharePoint Content Database Log file

Few times while inspecting the content databases of my sharepoint server, i found certain log files are unusually large compared to the mdf files and its eating up the space in the db server which is causing several other issues such as not able to add or delete content in the site, creating sub sites etc.

After searching many websites, i found the below useful way to reduce the size of log files i.e. by shrinking them.

Following are the commands that should be run in SQL Server Management studio


USE WSS_ContentDBName;
GO

BACKUP LOG WSS_ContentDBName_log TO DISK = 'filepath\*.bak' -- better to back up the file before shrinking it
   -- the recovery mode is to be made simple otherwise the shrinking will not happen
GO

ALTER DATABASE WSS_ContentDBName

SET RECOVERY SIMPLE;

GO

DBCC SHRINKFILE (WSS_ContentDBName_log, size to shrink) WITH NO_INFOMSGS

 -- you can mention to which size (in MB) to shrink your file. This parameter is optional. If nothing is specified, then it will be shrinked to the default size.GO

ALTER DATABASE WSS_ContentDBName

SET RECOVERY FULL;  -- after done with the shrinking, again make the recovery mode to FULL. recovery mode is to be made full because, when database backup is happening, then for full back up both the mdf and ldf will be taken back up. therefore recovery can be made at any point of time.


 

1 comment:

  1. Thanks Geetha,

    This was helpful piece of information and very precise.

    ReplyDelete