Warning: The following post talks about unsupported action in SharePoint Databases. Microsoft is clear about it. Viewer discretion is advised. This post is for information only. It’s at your own risk.
Why I’m taking these precautions? Because, modifying a SharePoint Database or its content could have a dramatic impact on the functionalities. If you don’t know what your doing, please don’t do it.
Here’s my story! We have a huge SharePoint Development Environment. Seven .Net Programmers who each hasa stand alone SharePoint 2010 development server. To minimize the SQL Licencing, all of them are using the same SQL Server environment. 186 databases in the same SQL Server, and because it’s a development environment, Storage Administrator set up a capacity limit on the database disk partition. Thus, we keep an eye on how much space we used. So far, nothing to report until this morning. During the week-end, the SQL Server went out of free space on the database partition. The responsible was a scheduled full crawl by one of the programmer on its server.
So, it was a day to clean. I began by checking the size of each MDF file in the database partition. I was surprise that all SharePoint Configuration Databases were 3 gigs or more. I opened up SQL Management Studio and I ran a Database size per table report on one of them. I found out the reason of this space appetite. 90% of the space used was on one table only: Timer Job History. I got more surprise. There is a Timer Job definition call Delete Job History that run every week. I opened up the table and I saw job entries four months before now. It’s great to have a job history list, but I don’t think I need to check so far in time.
Since all of configuration databases have data’s history four month before today, I was under the impression the job setting was set to 4 months. So I ran the following PowerShell cmdlets using the SharePoint 2010 Management Shell.
$test = get-sptimerjob | where-object {$_.name -like “job-delete*”}
$test.DaysToKeepHistory
The result was 7 days. What? 7 days. So why there’s 4 months of data in the table. Many of these Cmdlets call Stored Procedure in the database. So I opened up the Configuration Database in SQL Management Studio –> Programmability. There is a stored proc: proc_DeleteOldTimerJobHistory. I executed it and it required a date. It clean up the table all right. So it might be a bug of the TimerJob.
