SQL Server Master Database Maintenance

By: Eric Blinn

Overview

In this section we will cover questions like: do you need to shrink the master database and if so how, rebuilding indexes and running integrity checks for he master database.

Can you shrink the database and transaction log for the SQL Server master database?

The data and log files for the master database can be resized just the same as any other database.  This isn’t an operation that should be needed very often.  If, after reading this tutorial, a group of user objects is moved out of master and into a user database that might leave some empty space that can be reclaimed.  In a scenario such as that a shrink would be warranted.

The code below would shrink the master database data and log files to 16 and 8 megabytes respectively.  It assumes the default logical files names.

DBCC SHRINKFILE (N'master' , 16);

DBCC SHRINKFILE (N'mastlog' , 8);

Should you rebuild indexes for the SQL Server master database and what is the code?

The data within the master database are both small, and rarely changing.  As such index maintenance is unnecessary, but indexes can be rebuilt like any other database.

Should you run integrity checks for the SQL Server master database what is the code?

Yes!!  Very much yes.  It cannot be emphasized enough how import this task is.  The master database is of the utmost importance to a SQL Server instance.  Any corruption with the master database needs to be found as quickly as possible.  This is an operation that should only take a few seconds since the master database is so small – another reason to keep user objects out of master!  It should be run every day.  The code to run an integrity check of master is below.

DBCC CHECKDB ('master');