Stopping MSDB bloat due to frequent backups

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.
20150325 Bloated MSDB Initial Size
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

20150325 Bloated MSDB Biggest Space Users
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:
20150325 Bloated MSDB 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'

Then I checked the rowcount again:
20150325 Bloated MSDB 2011 rowcount I ran this several more times, keeping less history each time:

 EXEC msdb.dbo.sp_delete_backuphistory @oldest_date = '1/1/2012'

20150325 Bloated MSDB 2012 rowcount

 EXEC msdb.dbo.sp_delete_backuphistory @oldest_date = '1/1/2013'

20150325 Bloated MSDB 2013 rowcount 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'
20150325 Bloated MSDB 2015 rowcount 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.

Advertisements

One thought on “Stopping MSDB bloat due to frequent backups

  1. Pingback: Challenges with image-based backups | The Fisherman DBA

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s