SQL Server MSDB Database Maintenance

By: Eric Blinn

Overview

In this part, we look at the type of maintenance routines you should run on the MSDB database.

Can you shrink the database and transaction log for the MSDB database?

The data and log files for the MSDB database can be resized.  Like any other database, this isn’t an operation that should be needed very often.  If, after reading this tutorial, a more aggressive housekeeping routine is put in place to clear out some of the history tables, then maybe a one-off shrink is warranted.

The code below would shrink the MSDB database data and log files to 256 and 64 megabytes respectively.  It assumes the default logical files names.  This code would be run in the context of MSDB.

DBCC SHRINKFILE (N'MSDBData' , 256);
DBCC SHRINKFILE (N'MSDBLog' , 64);

Should you rebuild MSDB indexes and what is the code?

The data within MSDB is sometimes big and often changing as new rows are inserted into log tables while older rows are aged out.  As such, running traditional index maintenance against MSDB is wise.  If another maintenance job doesn’t already exist that can be made to include MSDB then a maintenance plan might be in order.  This screenshot shows one that was built using the wizard and all default values for just MSDB.

Should you run integrity checks on the MSDB database and what is the code?

Absolutely, yes!  It should be run every day.  So many SQL Server tasks depend on the SQL Server Agent and the SQL Server Agent depends on MSDB.  Without MSDB every backup operation will end with an error.  Finding and fixing MSDB corruption as soon as possible is of utmost importance to a SQL Server DBA.

The code to run an integrity check of MSDB is below.  Alternately, a step could be added to complete this if using a maintenance plan as shown in the previous step.

DBCC CHECKDB ('MSDB');