UPDATED: Gigantic Config DB… why… and how to get it back to normal
It has been pretty quiet around here but I’m trying to get some content up every now and then…
This was an issue I encountered recently with one of my clients. Their configuration datbase has grown up to 43GB and they couldn’t get it back to normal size which according to this TechNet Article is definied as small (~1GB) in size.
The first thing I have done to drill down into this issue was to open the disk usage by table report for the database within SQL management studio. This report showed me at the first glance that the timerjob history table size was very huge. Usually the timerjob history gets cleaned by a timerjob called “Delete job history” which by default runs every week
The next thing was to check the timerjob history in Central Admin and look for the last runs of this particular timerjob. And here I found the job failing for multiple weeks. When checking the error message it was pretty clear, the timerjob failed because the transaction log of the config db was full!!!
The next step was to check the settings for the transactionlogs in SQL server management studio and here I found the size of the transactionlog limited…After setting the size of the transactionlog to unlimited and running the timerjob the timerjob history table in the disk usage by table report shrunk back to a normal size and the database size could be normalized back to less than 1GB.
A good friend of mine Paul Grimley contacted me about this blogpost and highlighted that it would be helpful to update this article with some general guidance on database maintenance for SharePoint. Especially guidance on how we got the database size in the above described scenario back to normal.
In general I would recommend this TechNet article on SharePoint Database maintenance. It is for SharePoint 2010 but still valid for 2013. It also gives particular guidance on how to shrink data files.
Also I would like to share some articles Paul pointed me at which look at this topic from a supportability perspective:
- Support for changes to the databases that are used by Office server products and by Windows SharePoint Services (Please read this one very carefully!!!)
- Database Maintenance for Microsoft SharePoint 2010 Products
Plus extra Kudos to Paul for checking the delete history timerjob on his environment – which lead us to identify that the schedule of the timerjob has changed from weekly in SharePoint 2010 to daily in SharePoint 2013.
Hope this helps…