SharePoint: SharePoint_Config_Log is Large

I was looking at my backup logs and noticed how long it was taking to backup our SQL Server that is hosting our SharePoint databases.  Considering that we are just now adding content to it, it should not be taking as long as it is.  While looking at the databases, I discovered the SharePoint_Config_Log was over 100 GBs.  I found multiple blogs and articles giving information on how to resolve this, but it never seemed to work as they said.

I finally came across an article on Assorted Thoughts – Chaitanya Madala. This allowed me to shrink my database and recover a lot of hard drive capacity.

SOLUTION

Using SQL Server Management Studio:
1) Make a backup of the SharePoint_Config database
2) Execute the following query:

SQL Server 2008:

USE [master]
GO
ALTER DATABASE[SharePoint_Config] SET RECOVERY SIMPLE WITH NO_WAIT
GO
USE [SharePoint_Config]
GO
DBCC SHRINKFILE ('SharePoint_Config_Log')
GO
ALTER DATABASE[SharePoint_Config] SET RECOVERY FULL WITH NO_WAIT
GO
USE [SharePoint_Config]
GO

SQL Server 2005:

BACKUP LOG [Sharepoint_Config] WITH TRUNCATE_ONLY
USE [SharePoint_Config]
GO
DBCC SHRINKFILE (N’SharePoint_Config_log’ , 50)
GO

Now, this resolved the capacity the moment the command was run, but how to prevent this in the future?

I did some more searching and contacted a SharePoint Nerd saying that it’s commonly advisable to set the database recovery mode to Simple Recovery instead of Full Recovery. This will prevent the database log from increasing so much in the future, but I am still researching to ensure there are not going to be any implications in the future if I do this.

Advertisements
This entry was posted in SharePoint 2010, SQL Server 2008 R2 and tagged , , , . Bookmark the permalink.

One Response to SharePoint: SharePoint_Config_Log is Large

  1. Pingback: In case your SharePoint SQL server log file is getting huge | j5423

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s