Tim Radney

The Importance of Maintenance on MSDB

July 10, 2015 by in SQL Maintenance | 14 Comments
SQL Sentry Essentials
SentryOne Newsletters

The SQLPerformance.com bi-weekly newsletter keeps you up to speed on the most recent blog posts and forum discussions in the SQL Server community.

eNews is a bi-monthly newsletter with fun information about SentryOne, tips to help improve your productivity, and much more.

Subscribe

Featured Author

Itzik is a T-SQL trainer, a co-founder of SolidQ, and blogs about T-SQL fundamentails and query tuning.

Itzik’s Posts

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.