Resolve Out of space issue: SharePoint_config database

I’m running out of space. This seems to be a common problem with SharePoint. My latest adventure was more than your typical content database or SQL log files issues. This one is actually focused around SharePoint_config database.

Huh? Why is my SharePoint_config database so large?
It had me stumped for a bit. As I noted earlier I got an alert for low disk space on the SQL data drive. I looked into the typical… run away logs and things of that nature. Everything looked ok. Next, I sorted by SQL data folder by size. To my surprise my SharePoint_config database had grown to a whopping 183GB.
How could that have happened? Doesn’t it just store SharePoint farm configuration information? Yes, and more. So I started by looking at what tables were actually in the SharePoint_config database. As it turns out there are only 18 tables that are named to give you an idea of their functionality.

So I ran the following for following SQL query in the SQL Server Management Studio.

EXEC sp_MSforeachtable @command1=”EXEC sp_spaceused ‘?'”

I immediately saw the problem. The dbo.TimerJobHistory had bloated to inexplicable proportions. It had 190,000,000+ rows. So what happened? From this I could now see that time job history is stored in the SharePiont_config database in this specific table. I could easily see how this table could grow considering how timer job happy SharePoint is, but not this large.
At this point I broke open my old friend powershell on the SharePoint WFE. Running the following commands…
Get-sptimerjob | format-table name
From there I noted a timer job called… job-delete-job-history
It looks like the job we need. Let’s take a closer look

$history = get-sptimerjob | where-object {$_.name -eq “job-delete-job-history”}
$history | get-member

And then…

$history.historyentries

Bad news here. This displayed a listing of all entries from newest to oldest. The last entry is over a year old. This means SharePoint is not properly flushing these out. I ran this to see how long it was supposed to keep these entries

$history.DaysToKeepHistory

As it turns out it’s only supposed to keep them by default for 7 days, and I have over 365+ days of logs. We need to flush out these logs to get the SharePoint_config database back under control, and it needed to be done via SharePoint. It is always a bad idea to directly edit any SharePoint database. I first tried to execute the timer job.

$history.runnow()

I watched my SQL server slowly run out of space on the data drive. The job tried to run but it crashed and burned.
ErrorMessage: The transaction log for database ‘SharePoint_Config’ is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
Looking at that table it basically translates to, ‘I have a job waiting to run, but I can’t run it’. Space is my issue here. The log file for the SharePoint_config database grew, and grew, until it ran out of space. Once it ran out of space, it failed without committing any changes to actual database. Easy fix? Change the SharePoint_config to simple mode. That won’t work, and it is not the way SharePoint set the database.
So I have to work within the SharePoint system, and account for space. Here is how I did it…

$history.daystokeephistory = 365
$history.update()
$history.runnow()

Let the job complete, and keep decreasing, the $history.daystokeephistory until you get back down to the default 7 days. Don’t forget to update() the changes. Painful? Yes. Long? Yes, but it worked.


ref: http://www.techgrowingpains.com/2012/01/out-of-control-sharepoint_config-database/