I’ve always wanted to catch a truly fat fish. In fall many species of fish feed heavily to put on fat for the winter, and when catching them you can see the stomach pushing the belly of the fish out and making them look quite bloated. This is the reason that fishing in the cold wind and rain of late fall is worth the pain and sacrifice.
I recently ran into an issue where I noticed the MSDB database on an instance was growing quite bloated. MSDB database contains data related to SQL Agent jobs, database mail, SSIS packages, Backups, and Maintenance plans. In most cases it should be fairly small, but I observed it at close to 4 GB.
To find what was taking all the space I ran this query:
USE msdb SELECT OBJECT_NAME(i.OBJECT_ID) AS objectName , i.[name] AS indexName , SUM(a.total_pages) AS totalPages , SUM(a.used_pages) AS usedPages , SUM(a.data_pages) AS dataPages , ( SUM(a.total_pages) * 8 ) / 1024 AS totalSpaceMB , ( SUM(a.used_pages) * 8 ) / 1024 AS usedSpaceMB , ( SUM(a.data_pages) * 8 ) / 1024 AS dataSpaceMB FROM sys.indexes i INNER JOIN sys.partitions p ON i.OBJECT_ID = p.OBJECT_ID AND i.index_id = p.index_id INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id GROUP BY i.OBJECT_ID , i.index_id , i.[name] ORDER BY SUM(a.total_pages) DESC , OBJECT_NAME(i.OBJECT_ID) GO
This showed the backupfile table to be taking over half the database space, and some other associated backup tables taking up quite a bit of the rest. Looking in MSDN, I was able to find that the backupset table contains a row for each backup set. A backup set contains the backup from a single, successful backup operation. At he bottom of the msdn page I found this: “To reduce the number of rows in this table and in other backup and history tables, execute the sp_delete_backuphistory stored procedure.” I followed the hyperlink to the MSDN page describing this stored procedure. The page describes the stored procedure as follows: “Reduces the size of the backup and restore history tables by deleting the entries for backup sets older than the specified date. Additional rows are added to the backup and restore history tables after each backup or restore operation is performed; therefore, we recommend that you periodically execute sp_delete_backuphistory.”
I decided to get a baseline of how many rows are currently in the backupset table, then start deleting rows using this stored procedure. For this instance we never have to look at backups older than a month at the very most, but to be safe I would prefer not to delete backups that are too recent. I decided I wanted to reduce the record count by about 75%, and then monitor growth to see how much data I should actually keep.
Here is the initial rowcount:
Next I ran the stored procedure to delete everything older than January 1st 2011:
EXEC msdb.dbo.sp_delete_backuphistory @oldest_date = '1/1/2011'
EXEC msdb.dbo.sp_delete_backuphistory @oldest_date = '1/1/2012'
EXEC msdb.dbo.sp_delete_backuphistory @oldest_date = '1/1/2013'
I quickly realized much of this bloat is due to a change in the backup configuration we made late last year. In order to facilitate disaster recovery we had to abandon our native SQL backups (NO!) and go with a third party image backup system. I didn’t like this because I prefer to be in charge of the backups instead of leaving them to an outside vendor, but our cloud backup/disaster recovery area was only able to handle image backups, so I had no real option. Since we are busiest late in the year, we ended up having to change the transaction log backups so they are run every minute. This is the only way the image backup would work without slowing the system, since it requires an I/O freeze when it takes the backup, and the backup capture all changes since the last backup. Smaller intervals mean less changes and a shorter I/O freeze time.
After trimming the backup history to about three months:
EXEC msdb.dbo.sp_delete_backuphistory @oldest_date = '1/1/2015'
I was able to trim the rowcount enough such that there is significant empty space in the database. Next I set up a SQL job to delete all records older than 3 months and run it once a month. Now I don’t have to worry about MSDB continuing to grow.