Recently I have been asked to work on the task which is to reduce the size of 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 a big for database. Correct, but for MSDB, yes it is.
So, the question is - why the size has grown this much, and what would be the adverse impact it would have on the performance on 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 if 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 isn’t any CleanUp job 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 several – hundreds of databases which intern 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 OS and binaries resides – no RAID. Also, the size of the C drive is nearing to it’s capacity – 30 Gigs and it’s quickly filling up. Well, on top of this, the database is in FULL recovery model. The reason that MSDB grows to 20 Gigs are
- It never had CleanUp job on it,
- there are hundreds of databases keep inserting records for backup and restore
Adverse impact:
- Possibly, your backup would take longer than usual as it would take time to write backup and /or restore history
- You would see timeout error when you try to dig out the reason for backup job failure
What I did was, I have created a maintenance plan for CleanUp which will call sp_delete_backuphistory which will run cleanup for below tables, 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 addition 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, change the recovery model to simple and shrink it – we were able to shrunk the MSDB successfully and bring it down to 6.5 Gigs.
Constraints and possible options:
- We had a limited maintenance window to accomplish a task
- Another maintenance activity has to be performed once we are done
- Option: We could have script foreign keys and other constraint, drop constraints and keys, and delete the records. I haven’t opt this method because I personally never did this.
Take out from this post: Do health check, and, schedule a Cleanup task for MSDB to run on regular basis.
-- Hemantgiri Goswami (http://www.sql-server-citation.com/)
