The Importance of Maintenance on MSDB

MSDB is a system database used by SQL Server. MSDB stores all sorts of data, such as backup and restore history, SQL Agent job history, log shipping monitor history, SSIS packages, Database Engine Tuning Advisor data, and Service Broker queue data. Just like user databases, msdb needs regular maintenance, including index optimizations and, more importantly, regular purging.

Backup and Restore History

By default, there is no method to purge or delete backup and restore history from msdb. It is kept forever until you set up a manual or automated process to delete the data. By not purging this data, msdb will continue to grow, which means reading and writing to those tables can become slower and impact the speed of your backup jobs.

Most third party tools and reputable maintenance solutions include processes for clearing out backup and restore history to prevent this from becoming an issue. An easy way to know if you are purging backup history or not is to query msdb directly:

SELECT CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
  msdb.dbo.backupset.database_name,
  msdb.dbo.backupset.backup_finish_date,
  CASE msdb..backupset.type
    WHEN 'D' THEN 'Database'
    WHEN 'L' THEN 'Log'
  END AS backup_type
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
ORDER BY msdb.dbo.backupset.backup_finish_date;

If you have backup or restore history dating back more than 90 days, then you should investigate if there is a regulatory requirement that mandates that you must keep the historical information about those backups for a specific period. If there isn’t a requirement, then you should consider purging data older than a certain time period. Backup history isn’t needed in order to restore your databases, and we recommend purging it on a regular basis to keep msdb at a reasonable size. Keeping 90 days or less is the range I typically recommend to clients.

To setup a process to purge the backup and restore history, create a job that executes the sp_delete_backuphistory stored procedure in msdb and pass it a date parameter. The stored procedure will delete all backup and restore history older than the date you provide. You can also create a Database Maintenance Plan and use the Clean Up History task.

Database Engine Tuning Advisor

The Database Engine Tuning Advisor, also known as DTA, is a tool that developers and Database Administrators can use to help tune a database. DTA leverages the msdb database to store tuning history and other supporting objects.

I routinely find remnants of DTA in msdb on clients’ production servers. When I find these tables, I query them directly to determine if DTA is still being used. Luckily, I have yet to find a client actively running DTA against production, as it can significantly impact performance. Once I confirm and communicate with the client, I drop the DTA tables from msdb. In some cases this frees up multiple gigabytes of space. As a precaution, I also take the time to explain the performance impact that running DTA against production can cause and encourage my clients that any future use should be done on a development server.

SQL Server Agent

On occasion, I will find a client who inadvertently unchecked the box to limit the size of the job history log. This is an easy mistake to make if you have a busy server and the log keeps rolling over so quickly that you don’t have any useful job history to reference when troubleshooting SQL Server Agent jobs. A better approach is to increase the maximum job history log size (in rows) to a much higher value rather than leave it to grow unrestricted.

In the cases where clients had unrestricted job growth, the sysjobhistory table had grown excessively large and needed to be purged. The best way to purge the history is to use sp_purge_jobhistory and pass in a date parameter. The stored procedure will delete all job history older than the date you provide. If you must keep a minimum number of days of SQL Server Agent history, limiting the job history log based on rows isn’t effective. Instead, do not limit the size of the job history log and also schedule a job that will run sp_purge_jobhistory and pass in a date parameter for the minimum number of days of job history that you need. It is common to use a value of 14 or 30 days.

Service Broker

Recently I encountered an issue with a client where msdb had grown to 14GB in size. After an attempt to update the instance to a current service pack, the upgrade failed applying the scripts to msdb and caused msdb to grow exponentially again. After some research we discovered Service Broker was enabled for event notifications but it was not properly configured. For over a year event notifications were being queued, but were not being routed.

In checking the sys.transmission_queue I found that the service broker in the target database was unavailable, and service broker was administratively disabled. I then checked to see what event notifications were setup by querying sys.server_events_notifications and found only one entry: capture all error log events. I then queried sys.transmissions_queue to see how many events were in the queue and found several million records there.

After discussing this with the client and explaining the findings, we agreed that the best course of action was to drop the event notification and clear the current queue by creating a new broker. To do this I executed ALTER DATABASE msdb SET NEW_BROKER. This was done after hours and after a good full backup of msdb.

After clearing the transmission_queue and removing the event I was able to reduce msdb from 14GB to 300MB. Prior to correcting this issue, the msdb database had the highest disk latency on the instance and the client was experiencing regular deadlocks. After implementing this change, as well as other optimizations, the client’s user experience greatly improved.

Log Shipping

Early in my DBA career I inherited a consolidation server that had a few hundred databases that were Log Shipped to a secondary server in another datacenter. This server had been up and running for several years and was shipping the logs every 15 minutes. Not only did this instance suffer from not purging the backup history, it also was not properly clearing the Log Shipping monitor history. Once I purged the backup history and checked the size of msdb it was still showing more used space than it should. I ran a script to show me the total size of each table and found that the log_shipping_monitor_history_detail table was very large. In this case I was able to run sp_cleanup_log_shipping_history to purge the history and get msdb back to normal size.

Indexing

Optimizing indexes in msdb is just as important as your user databases. Many times I have found clients who are optimizing user databases but not the system databases. Since the msdb database is heavily used by SQL Server Agent, Log Shipping, Service Broker, SSIS, backup and restore, and other processes, the indexes can get highly fragmented. Ensure that your index optimization jobs also include your system databases, or at least msdb. I’ve seen index optimizations free up several gigabytes of space from highly fragmented indexes within msdb.

Summary

Neglecting msdb can negatively impact the performance of your environment. It’s crucial to monitor the size of msdb, as well as the processes that use it, to ensure that it performs optimally. Backup and restore history is the most common reason for the msdb database to bloat, however Database Engine Tuning Advisor, SQL Server Agent history, service broker, log shipping and lack of index maintenance can all contribute to excessive growth of msdb and impact the performance of the database.

14 thoughts on “The Importance of Maintenance on MSDB”

Chris Wood says:

July 10, 2015 at 2:53 PM

Tim,

In about 2006 I discovered that Geoff Hiten @SQLCraftsman had written a script to add indexes to msdb:-

use msdb 
go 

--backupset 
Create index IX_backupset_backup_set_id on backupset(backup_set_id) 
Create index IX_backupset_backup_set_uuid on backupset(backup_set_uuid) 
Create index IX_backupset_media_set_id on backupset(media_set_id) 
Create index IX_backupset_backup_finish_date on backupset(backup_finish_date) 
Create index IX_backupset_backup_start_date on backupset(backup_start_date) 

--backupmediaset 
Create index IX_backupmediaset_media_set_id on backupmediaset(media_set_id) 

--backupfile 
Create index IX_backupfile_backup_set_id on backupfile(backup_set_id) 

--backupmediafamily 
Create index IX_backupmediafamily_media_set_id on backupmediafamily(media_set_id) 

--restorehistory 
Create index IX_restorehistory_restore_history_id on restorehistory(restore_history_id) 
Create index IX_restorehistory_backup_set_id on restorehistory(backup_set_id) 

--restorefile 
Create index IX_restorefile_restore_history_id on restorefile(restore_history_id) 

--restorefilegroup 
Create index IX_restorefilegroup_restore_history_id on restorefilegroup(restore_history_id)

Do these changes still make sense?

Thanks

Chris

Chris Wood says:

July 10, 2015 at 3:11 PM

And would you update the stats as well as rebuilding fragmented indexes? We use Ola's utilities for index maintenance.

Chris
Aaron Bertrand says:

July 10, 2015 at 6:36 PM

Chris, you'll find that many of those indexes are in the product in modern versions, though I don't know when Microsoft started putting them in there. There are a few missing, but I would only consider adding those if you have evidence that they would be beneficial.
Mark says:

July 13, 2015 at 3:55 PM

Thanks for the insights.While we were OK on all these items, I'm going to add checks to my monthly maintenance batch.

We're on 2008 R2; A while back I found that my database mail tables were getting out of control, so I started clearing older records on a regular schedule. Not sure if there was supposed to be a regular purge task that we overlooked, or whether newer versions have one built in, but it may be worth looking at if the Database Server is tasked with sending mail.
Chris Wood says:

July 13, 2015 at 4:01 PM

We have also found Policy Management (Set HistoryRetentionDays to something other than 0 the default) and SSIS log data needs to be managed in msdb.

Chris
Tim says:

July 14, 2015 at 3:22 PM

Wondering this myself.
Tim Radney says:

July 14, 2015 at 3:26 PM

I use Ola's scripts as well and let them perform index and statistics maintenance on the system databases as well.
Ekrem Önsoy says:

July 20, 2015 at 8:27 AM

sysmail_mailitems table must be added to the list.

John Burnette says:

July 27, 2015 at 6:07 PM

You also need to routinely purge the mail items and mail log (below is the code we use to only store 6 months — this is called via a job once a week during maintenance):

DECLARE @MinBackupDate DATETIME;
SET @MinBackupDate = DATEADD(month,-6, GETDATE());
DECLARE @SQL VARCHAR(MAX);

SET @SQL = 'EXEC msdb..sysmail_delete_mailitems_sp @sent_before= ''' + CONVERT(VARCHAR(20),@MinBackupDate,101) + ''' ;' ;
--PRINT @SQL;
EXEC( @SQL );

SET @SQL = 'EXEC msdb..sysmail_delete_log_sp @logged_before= ''' + CONVERT(VARCHAR(20),@MinBackupDate,101) + ''' ;' ;
--PRINT @SQL;
EXEC( @SQL );

Wilfred van Dijk says:

July 28, 2015 at 7:01 AM

You also have to take a look at these SP to cleanup your MSDB:

– sp_purge_jobhistory
– sysmail_delete_mailitems
– sysmail_delete_log_sp

If you want to control how much data is stored in MSDB, you also need to run this statement EXEC msdb.dbo.sp_set_sqlagent_properties @jobhistory_max_rows=-1, @jobhistory_max_rows_per_job=-1 this prevents storing max number of rows for each jobstep.

If you're using the scripts from Ola to rebuild your MSDB indexes make sure you set the flag MSShippedObjects = 'Y'

Aaron Bertrand says:

July 29, 2015 at 1:23 PM

Why all the dynamic SQL?

DECLARE @cutoff DATE;
SET @cutoff = DATEADD(MONTH, -6, GETDATE());
EXEC msdb..sysmail_delete_mailitems_sp @sent_before = @cutoff;
EXEC msdb..sysmail_delete_log_sp @logged_before = @cutoff;

Chris Wood says:

July 29, 2015 at 7:58 PM

Had to set Ola's MSShippedobjects = 'Y' to see something on msdb.
Time to add this to the schedule.
Nathan Sunderman says:

March 1, 2017 at 12:11 AM

Thanks Chris, this is what I was looking for…

-Nathan
Solomon Rutzky says:

January 25, 2018 at 3:12 PM

While others have mentioned dbmail (and I am repeating it for visibility / completeness), I will also mention that the Central Management Server (CMS) stores data in [msdb], which includes shared Registered Servers lists. A Registered Servers list does not change frequently and so shouldn't need much index maintenance, but it is yet another MS feature dependency on [msdb] that folks should be aware of.

Comments are closed.