Production DBA
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:
|
1 2 3 |
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:
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.
Free, 3× a week
Get my new posts by email
Three posts a week, plus a Monday roundup of the best database news from around the web.
Forgive me for being slow, but how can you tell those 3 have problems and not the others with dates preceeding 8/21?
Check the year field. 😉
Check the year.
Ah, oops. However, this gives me a good idea for a blog entry. 🙂
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!
See http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1704.aspx for a script to more efficiently clear out the backup history.
Thanks Brent, David and Mufasa.
Does anyone who uses SQL Agent Jobs should run similar task for cleaning jobs log regularly? I’ve found sp_delete_jobsteplog, and BOL says it’s purpose is “Removes all SQL Server Agent job step logs that are specified with the arguments. Use this stored procedure to maintain the sysjobstepslogs table in the msdb database.”
Unfortunately, sp_delete_jobsteplog is not available on SQL Server 2000. Yeah, we still use it, but going to migrate. Fortunately, there is sp_purge_jobhistory “Removes the history records for a job.” which removes all log entrines for job/step specified.
But there are even more space-cloggers in msdb – tables prefixed with DTA_. Right now msdbdata.mdf size is 1644Mb and there are 2,912,825 rows in DTA_reports_querycolumn_index table! These are because of DB Engine Tuning Advisor.
Should I just drop rows from it’s tables because we don’t need it?
IL – good question. I haven’t worked with those tables, so I’m hesitant to say. I would probably truncate ’em though.
Brent, I’ve cleaned DTA* tables by
truncate table DTA_reports_querycolumn
truncate table DTA_reports_indexcolumn
truncate table DTA_reports_queryindex
truncate table DTA_reports_querytable
truncate table DTA_reports_querydatabase
truncate table DTA_reports_tableview
truncate table DTA_reports_partitionscheme
truncate table DTA_tuninglog
truncate table DTA_output
truncate table DTA_progress
delete DTA_input
delete DTA_reports_column
delete DTA_reports_index
delete DTA_reports_query
delete DTA_reports_table
delete DTA_reports_database
delete DTA_reports_partitionfunction
and msdb returned to 14Mb size after shrinking. Thanks again for good advice to clean msdb regularly.
Cool, glad I could help! I think I’ll add this to my Blitz checklist too.
oh look, i just #sqlhelped this!
i heart this here blog entry. i remember and reference it, a whole year later.
Hahaha, wow – I didn’t know you were reading my blog a year ago!
Brent,
How about the sysjobstepslogs table? It occupies 95% space on my msdb database.
sp_delete_jobsteplog would delete records on this table.
Sivaprasad – that’s a great question, but I haven’t run into that issue myself, so I can’t say.
Thanks for this, I was trying to figure out how to do this for like 2 months and of course the answer was under my nose.
Clean and simple.
Very nice usage of multi-server-query-execute feature.
I like it.
Roni.
I have uncheck “Limit size of job history log”
and make a maintenance plan to delete agent job history older than 2 months.
however, I still find the job doesn’t help me to keep 2 months, ( i think it only keep 25-30 days only, depends on different job……..)
Michael – is that a question? (I’m not sure – can you reword it?)