I recently encountered a situation where the drive hosting Sharepoint Databases in a Staging environment ran out of space. I logged onto the server and found that the msdb database has itself occupied 38 GB of the total disk space. Msdb database generally contain maintenance information for the database such as backups, log shipping and so on. My first step was to examine all the tables and I noted that there was not an abnormally large number of records.

I then decided to verify using a T-SQL script (shown below) exactly who the culprit actually is and the results were rather strange.

The output of the above T-SQL query is as shown in the screen capture below:

1_SQL_Server_Controlling_Growth_of_a_msdb_Database

As you can see in the first highlighted section we can see that there are two indexes named c1lsmonitor_history_detail, and nc2lsmonitor_history_detail is present in the table named log_shipping_monitor_history_detail which has occupied 25698+9899=35597 MB= 34.76 GB and these were the primary cause of the large database size.

I then decided to perform a Re-index of the two indexes noted above and I did this by just right-clicking on the Index Name and selecting Rebuild.I also updated the statistics of the corressponding indexes. After completing the Re-indexing and Update Statistics, I tried to Shrink the msdb database and it shrank the database size from 35 GB to a mere 700 MB. please refer the screen capture below:

2_SQL_Server_Controlling_Growth_of_a_msdb_Database

I then decided to include the msdb database as a part of daily reindexing and update statistics job which is set to occur daily.

Reindexing and updating statistics could alternatively be accomplished using the T-SQL below.

T-SQL for Re-indexing:

T-SQL for Update Statistics:

Thus we have successfully controlled the growth of the Distribution Database. Please let us know if you have any suggestions or comments on this approach.

 

Regards

Satnam Singh

Like us on FaceBook  |  Follow us on Twitter

Follow me on FaceBook| Join the fastest growing SQL Server group on FaceBook