Out of control SharePoint_Config Database

Oh my. Oh my. 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.

-Javi

About the Author

6 thoughts on “Out of control SharePoint_Config Database

    • Author gravatar

      event thought I have not try it, It looks working and very clear. Don’t u use SQL transaction?

    • Author gravatar

      I am curious to know what you mean by “don’t forget to update() the changes. Sorry I am new to this….

    • Author gravatar

      For development/QA/Test environments (NOT PRODUCTION!!!) I recommend:
      1. Open up SQL Server Management Studio (SSMS) for the SQL Server that holds SharePoint databases.

      2. In a new query window:
      USE [master]
      GO

      ALTER DATABASE [model] SET RECOVERY SIMPLE WITH NO_WAIT

      GO
      (the above makes sure any NEW SQL databases will start out life with Recovery Model Simple – the easiest one to reclaim disk space from)

      3. For the existing, possibly bloated SharePoint databases, substitute the appropriate value for your situation in the following commands:

      use [master]
      GO

      ALTER DATABASE [change-this-value] SET RECOVERY SIMPLE WITH NO_WAIT

      4. Bring up SSMS Object Explorer window (keyboard short-cut F7)

      5. Expand Databases in the tree view.

      6. Single-click on one of the SharePoint databases, for example SharePoint_Config, to highlight it.

      7. Right-click on that same database, and choose Properties.

      8. In the “Select a page” section of the resulting pop-up window, single-click on Options.

      9. If the Recovery Model already says Simple, you can click Cancel. If it says anything else, pop open the dropdown listbox and choose Simple, followed by OK.

      10. Repeat steps 6-9 for each SharePoint-related database.

      11. Return to SSMS Object Explorer (F7 if it’s no longer visible).

      12. Expand the Management section of the tree structure – you might not see Management if the user you’re logged in with doesn’t have sufficient SQL Server privileges.

      13. Right-click on Maintenance Plans and choose Maintenance Plan wizard.

      14. Click Next.

      15. Choose a name such as ShrinkDbs and optionally fill in a description. Click Next.

      16. When the set of checkboxes appears in the next screen, check only Shrink Database and click Next.

      17. Click Next again to access the Task Order defaults.

      18. In the databases dropdown, choose All User Databases and check the Ignore database where the state is not online checkbox.

      19. In the Define Shrink Database Task dialog, change the two checkboxes to 1 and leave the radio button choice of return freed space to operating system set. Click Next.

      20. Click Next again.

      21. Click Finish and wait for the Wizard to finish all 5 of its tasks before clicking the Close button.

      22. Return to SSMS Object Explorer (use F7 if necessary to make it visible).

      23. Right click on your newly created Maintenance Plan and choose Execute.

      24. Wait until the pop-up window indicates Success and then click Close.

      At this point your SharePoint databases, particularly the SQL Server log files (the ones that have a .ldf exetension) are the smallest they will ever be. Keep in mind that as SharePoint timer jobs and other SharePoint database activities run, those .LDF files will begin to grow again, so make sure you execute that Shrink Databases plan on a regular basis; otherwise, the same issue will happen again.

    • Author gravatar

      Thanks !!
      I added the commands in ps1 script and executed with below
      for($i=1;$i -le 100; $i++)
      {
      Write-Host $i
      . ‘Timer.ps1’
      Start-sleep -Seconds 1
      Write-Host ” ”
      }

Leave a Reply

%d bloggers like this: