MSDB – Cleanup is necessary

Recently I have been asked to work on the task which is to reduce the size of a MSDB database. The MSDB has grown to 20 Gigs. Well, you would say, what is the big deal with that? 20 Gigs is not considered big for a database. Correct, but for MSDB, it is.

So, the question is - why has the size grown this much, and what is the adverse impact it may have on the performance of my system?

I would say, there could me many reasons, like:

• Usually, we do not create user objects inside MSDB, but it is good to check. Check if there are multiple SSIS/DTS Packages that are large. Check with development team whether you can store them in file system. Check the link for the list of tables refer links SQL Server 2005, SQL Server 2008 , SQL Server 2008 R2 , and SQL Server 2012
• There aren't any CleanUp jobs configured
• There are several hundreds of jobs running i.e. LS aka Log Shipping
• And, so on….

The case:

Client has a server configured with LS for DR purpose. The LS is configured to sync every 15 minutes for hundreds of databases which involves inserting lots of data into the historical tables like backup, restore and log_shipping_monitor_history table –all of them had > 75 Lacks of records.

The issue for us was that the MSDB is configured on the local drive (aka where the OS and binaries reside) – no RAID. Also, the size of the C drive is nearing capacity – 30 Gigs and it’s quickly filling up. Well, on top of this, the database is in FULL recovery model. The reasons that MSDB grows to 20 Gigs are:

1. It never had a CleanUp job done
2. There are hundreds of databases that keep inserting records for backup and restore

Adverse impact:

1. Possibly, your backup would take longer than usual as it would take time to write backup and /or restore history
2. You would see timeout errors when you try to dig out the reasons for backup job failure

What I did was create a maintenance plan for CleanUp which will call sp_delete_backuphistory which will run cleanup for the tables below. Per client’s request, I have configured job to remove all the older data before 60 days.

• backupfile
• backupfilegroup
• backupmediafamily
• backupmediaset
• backupset
• restorefile
• restorefilegroup
• restorehistory

To complete the cleanup activity job successfully, the MSDB and tempdb will need some space to grow, which is not possible in our case since we are left with only 10 Gigs of space. Hence, I have added an additional log file and data file for tempdb and msdb on another drive where we have ample space. Schedule LOG backup for MSDB to run every 10 minutes.

And, then, I’ve invoked the CleanUp job – it took about 3 hours to finish, but it did what it should.

Took FULL backup for MSDB. Changed the recovery model to simplify and shrink it – we were able to shrink the MSDB successfully and bring it down to 6.5 Gigs.

1. We had a limited maintenance window to accomplish a task
2. Another maintenance activity has to be performed once we are done
3. Option: We could have scripted foreign keys and other constraints, dropped constraints and keys, and deleted the records. I didn't choose this method, because I've personally never done it.

Takeaway from this post: Do health check, and schedule a Cleanup task for MSDB to run on a regular basis.