Check All Your MSDB Cleanup Jobs With One Query

Are all of your servers set up to clean out their backup history periodically?

Are you sure?

If you looked back at the monitor with “WTF?” written all over your face, I’ll bring you up to speed.  Here’s the problem: SQL Server stores information about completed backups in the MSDB database.  In older versions of SQL Server, that database isn’t indexed very well, and it can become a performance bottleneck during backups and restores.  I’ve written about one particular case where I cut the company’s backup times by 2/3 just by cleaning out their backup history.

To make sure your servers are keeping their MSDB clean, you can use SQL Server Management Studio 2008’s new multi-server-query-execute feature to quickly query all of your servers.  Go read my article on how to query multiple servers at once in SSMS, and then run this query against your SQL Servers:

SELECT TOP 1 backup_start_date
  FROM msdb.dbo.backupset WITH (NOLOCK)
  ORDER BY backup_set_id ASC

The query runs almost instantaneously since it’s sorted by the primary key of the table.  The results of the query are shown here:

Consolidated MSDB Backup History

Consolidated MSDB Backup History

In that screenshot, I can see right away that three of the servers in my lab have problems.  They don’t have cleanup tasks properly configured.

If you see results like this in your own environment, drop what you’re doing and go add MSDB cleanup tasks to your maintenance plans or add sp_delete_backuphistory to your T-SQL maintenance scripts.  The first time it runs, you may run into some horribly long runtimes and locks, unfortunately.

7 Responses to Check All Your MSDB Cleanup Jobs With One Query
  1. David Stein
    September 4, 2009 | 9:57 AM

    Forgive me for being slow, but how can you tell those 3 have problems and not the others with dates preceeding 8/21?

  2. Jeff
    September 4, 2009 | 10:26 AM

    Check the year.

  3. David Stein
    September 4, 2009 | 11:06 AM

    Ah, oops. However, this gives me a good idea for a blog entry. :)

  4. David Hay
    September 4, 2009 | 3:57 PM

    Fantastic! I saw this when you posted it originally but didn’t have the time to check then. Glad you did a recap of it as I now had the time. Records going back years is right! I had some going back to January of 2001! I had over 200k records in one of my backupset tables. It’s a good thing we only use simple and daily full backups at this point. I set a limit on how far back we want to save, created a job to run the sp_delete_backuphistory. I also added a step to reindex the tables it touched as well. You might not need the backupfilegroup table, as it doesn’t exist on all my servers I’m nto sure why. Probably only if you have ever performed a filegroup backup I’m not sure. These could always be werapped in an exists statement too.

    dbcc dbreindex (‘backupfilegroup’, ”) ;
    dbcc dbreindex (‘backupfile’, ”) ;
    dbcc dbreindex (‘backupmediafamily’, ”);
    dbcc dbreindex (‘backupmediaset’, ”);
    dbcc dbreindex (‘backupset’, ”);
    dbcc dbreindex (‘restorefile’, ”);
    dbcc dbreindex (‘restorefilegroup’, ”);
    dbcc dbreindex (‘restorehistory’, ”);

    Thanks Brent!

  5. Mufasa
    November 3, 2009 | 8:03 PM

    See http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1704.aspx for a script to more efficiently clear out the backup history.

Trackbacks/Pingbacks
  1. Weekly Link Post 109 « Rhonda Tipton’s WebLog
Leave a Reply


Wanting to leave an <em>phasis on your comment?

Trackback URL http://www.brentozar.com/archive/2009/09/checking-your-msdb-cleanup-jobs/trackback/
WOOHOO!
Mar 3 - Day with the DMVs - free day-long webcast about performance tuning.

Apr 17 - SQLSaturday Chicago - I'm doing the keynote with Kevin Kline.

More Upcoming Events