Massive SharePoint config database log file

Its a common problem that many of us have. You set up your SharePoint farm, and its been running sweet as a nut, till you get asked one difficult question by the server team… why is the SharePoint configuration database log file so large?

SharePoint 2007 creates its databases in FULL RECOVERY mode. This is presumably because the SharePoint central administration pages were going to offer a more comprehensive recovery option, however I’ve only ever seen a full database restore. However, even though the built in GUI doesn’t make the use of the FULL RECOVERY model, I don’t think we should discount it.

There are two (nice) ways of coping with the log growth issue;

  1. You use your own backup strategy [NB this means you can’t use the built in restore option anymore], e.g. Scheduled SQL Job to backup the databases and logs yourself. When you do this, you have the option to use the WITH TRUNCATE ONLY option, or…
  2. Use the built in SharePoint backup/restore service from Central Admin, and schedule log truncations at defined intervals. (see below snippet of code shamelessly stolen from the Microsoft DBCC Shrink file tutorial)

– Example truncating a config db logfile to 70 MB):
BACKUP LOG [Sharepoint_Config] TO DISK=’drive:\path\Yourbackupfile.bak’
GO
BACKUP LOG [Sharepoint_Config] WITH TRUNCATE_ONLY
USE [SharePoint_Config]
GO
DBCC SHRINKFILE (N’SharePoint_Config_log’ , 70)

– Note: the 70 is the 70MB.  
GO

There is an excellent article on Death By Patterns website about log file management. This gives you an overview of why logs grow, and how they work in practice.

It should be noted that in more than a few places on the net, people recommend changing the database mode from FULL to SIMPLE in order to perform a DBCC SHRINKFILE. Whilst this is a simple and effective solution it takes away a crucial service that administrators can perform on a live SharePoint farm, if something goes wrong during the day, before a backup takes place, you have the capability of doing a in place transaction rollback.

See Sherin George’s blog for an excellent overview of the recovery models available in SQL Server.

See Server Fault discussion on SharePoint recovery models.

SC




You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

One Response to “Massive SharePoint config database log file”

  1. Office 2007 Says:

    Great article, thanks for the share. Blog bookmarked :)

Leave a Reply