One day, I will create a CodePlex Project to fix all the annoying things in SharePoint. You know these things that don’t affect the functionality but can make you life easier.
When I’m using the UI to install SharePoint 2010, I always wonder why at some point, I don’t have the possibility to add the database name myself. Like for the SharePoint Admin Content Database or the Search Services Databases, or the WSS Usage Database. On the other hand, some services offers the require fields to enter a database name. Of course, PowerShell come to the rescue. Great. But why I’ve need to do it that way when only a simple Database Name field would do the job… That my friend, it’s the mystory of life… Why do simple things when complicated one can be afford… I love my job by the way.
In the following table, I’ve put some link to great articles that help it to achieve this task. New links will come.
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.
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.