Programming, SharePoint, Software, SQL, SQL Server 2005, SQL Server 2008, SQL Server 2012, SQL Tips, Transact-SQL, tSQL

SharePoint Dealing with the Huge and Growing File Size of SharePoint_Config_Log.ldf

SP_Custom_Menu
SP_Custom_Menu

It’s amazing how some SharePoint issues are known problems that don’t get fixed by Microsoft even after many years, and that don’t get mentioned as things one needs to look out for. I found this out recently when I ran up against an error where the entire disk space of my SharePoint SQL Server instance was filled up by a file called SharePoint_Config_Log.ldf . This is ever growing file is apparently a known problem with SharePoint that has existed in many earlier versions and that remains difficult to find a solution for that actually fixes the problem.

How the Problem Showed Up

Just as to give some background, I have more or less a stock installation of SharePoint 2016 Standard edition that consists of a SharePoint Application server connected to a SharePoint Database Server. Prior to that I was testing SharePoint 2013 Enterprise Edition for a few months buy where this issue never cropped up.

Imagine my surprise when one day the drive of the database server was completely full. Only by using the tool WinDirStat and then by doing a lot of Google searches was I able to find the culprit: the SharePoint database file SharePoint_Config_Log.ldf

According to various online help sources, this is a known issue that has been plaguing SharePoint administrators for several generations, with no Microsoft fix in sight, and an incredible amount of incorrect advice on how to fix this. Needless to say, I think Microsoft should handle known issues like this better.

An Actual Solution that Works

Finally, I was able to track down a manual solution, which is to run a SQL script in SSMS with an administrative account on the database server. I found a SQL script that actually fixes the large file size on the following site:

https://www.qdoscc.com/blog/sharepoint-tips-sharepointconfiglogldf-file-size-massive

Like I mentioned, I looked at many support articles, each of which had differing advice, and none of which actually did anything. However, here is the SQL that did the trick and reduced the file size from several Gigabytes.

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

Great, I’m glad this actually worked. The next step will be to automate the maintenance so that a database or a Windows job can execute the script automatically when the SharePoint_Config_Log.ldf file grows beyond a certain size. I’ll post this routine once I get a chance to put it together.

Advertisements

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