Let’s talk about maintenance  of system databases in MS SQL Server. There are a few differences from user databases.

At first let`s remember what system databases we have and what do they stay for.

Master – this database keeps all system-level information of SQL Server instance.

Model – this databases is used as a template for all new databases created in SQL Server. Changing of the size, sorting options, recovery model or any other parameters of  model database will lead to having the same values of those parameters in all databases created after you change model..

Msdb – used by SQL Server agent for storing alerts and jobs, also it stores SSIS packages, information about backups and restores etc.

tempdb – database for temporary objects and for intermediate result sets.

Resource – read-only databases which contains system objects  which are part of SQL Server. System objects are actually kept in database Resource, but logically displayed in the sys schema of other databases.

 System databases maintenance is aimed at having an ability to restore data of these databases in case of disaster, failed upgrade, equipment failures and any other possible problems.

Typical service tasks for system databases (except TempDb and database Resource):

• Backing up the database (with a retention period of at least 7 days);
• Integrity check of the database with DBCC CHECKDB command.

All these operations can be put into SQL Server Agent job  and  performed on daily basis cause the volume  of data in these databases is usually small, so maintenance operations go quite fast…and add peace of mind.

But it’s not all; we`d like add more about msdb database because there msdb  specific maintenance tasks which should be done along side with the tasks mentioned above.

As you know, msdb database  stores the history of backups for databases. Now imagine the server, which has more than 50 databases, every 10-15 minutes creating transaction log backup. What will be the size of tables with this information?

One of our clients servers when we first time get there had over 70 databases, and the server was more than 2.5 years old … and the information on the backups has  never been cleaned. As a result msdb database size was more than 20 Gb! Given such database size it will take some time to create backup, run  integrity check of the database, also it will add extra disk activity, take additional time for disaster recovery; as a result we have many disadvantages, which we can easily to solve.

Backup history purging is made via a procedure:

sp_delete_backuphistory [ @oldest_date = ] 'oldest_date'

where

            [ @oldest_date = ] ‘oldest_date’ 

the earliest date stored in the log table for backup and recovery. Parameter oldest_date has datetime type and does not have default values.

Backup information has been cleaned, what else is stored there?

Mail. Have you set up Database Mail? Does it send e-mails? May be with attachments? 

The whole history of it is also stored in the database msdb. To clear this stuff, there is also a system procedure:

sysmail_delete_mailitems_sp  [ [ @sent_before = ] 'sent_before' ] [ , [ @sent_status = ] 'sent_status' ]

where

[ @sent_before = ] ‘sent_before’ 

Deletes e-mail messages before the date and time specified by parameter sent_before. Parameter sent_before has datetime type and does not have default values. NULL  matches all dates.

[ @sent_status = ] ‘sent_status’

Deletes e-mail messages of the type specified by parameter sent_status. Parameter  sent_status has varchar(8) type and doesn`t have default values. Valid values: sent, unsent, retrying  and  failed. NULL meaning matches all states.

sysmail_delete_log_sp  [ [ @logged_before = ] 'logged_before' ] [, [ @event_type = ] 'event_type' ]

where

[ @logged_before = ] ‘logged_before’

deletes the records up to the date and time specified in the parameter logged_before. Parameter logged_before has datetime type and default values NULL. NULL meaning matches all dates.

[ @event_type = ] ‘event_type’

Deletes journal entries of a certain type, specified by parameter event_type. Parameter  event_type has varchar(15) type and doesn’t have default values. Valid values: success, warning, error and informational. NULL matches all types of events.

We`re done with mail and old information, what else can still be there?

Do you have SSIS packages and how often do they run? Their run history is stored in the table [msdb].[dbo].[sysssislog].

If you want to clean it, run a simple statement:

DELETE   FROM [msdb].[dbo].[sysssislog] WHERE starttime<@dt

Where  @dt – records created  before this date will be deleted.

We should put all these operations into SQL Server Agent job:

– Remove backup history

– Cleaning the Database Mail

– Cleaning history table [msdb]. [Dbo]. [Sysssislog]

Running  this job for example  twice a month will allow us to have small system databases.

Part of these operations is done automatically during the creation of Maintenance Plans, but we seldom use it, so we always have a script for a job to perform maintenance of  system databases, which is simply ran on each new databases server we get.

If we missed something will be glad to get comments.

Be careful and keep the workplace clean!