Vitor Montalvão

SQL Server system databases – the msdb database

July 14, 2017 by

Introduction

This article is the third I am writing about Microsoft SQL system databases.

In this article, I will focus only on the msdb database, one of the four system databases that exist in any MSSQL instance:

msdb usage

The msdb database is mostly known as “the SQL Server Agent database” because it stores information of all SQL Agent jobs as their configuration and their execution history.

But it is not dedicated mainly to SQL Server Agent. Features like the Service Broker or the Database Mail also stores data in msdb database.

The msdb database it is also used to store information about all backups and restores that are executed, no matter if they were executed by a SQL Agent job or ran by SSMS, sqlcmd or any other application (e.g. backup solutions as NetBackup), msdb will keep track of all related operations.

Roles

One particularity of the msdb is that is the only system database that has pre-defined database roles besides the regular fixed database roles ones. Besides the members of the sysadmin fixed server role that are able to perform any operation in the MSSQL instance, only members of the following database roles are able to execute the related operations (for better understanding I have grouped these database roles):

Group name Database role name Notes
Database mail DatabaseMailUserRole Only members of this database role are able to send mails through SQL Server.
Integration Services Roles db_ssisadmin
db_ssisltduser
db_ssisoperator
Only members of this database role are able to work with SSIS packages.
Data Collector dc_operator
dc_admin
dc_proxy
Used to implement the Data Collector Security.
Policy-Based Management PolicyAdministratorRole Only members of this database role are able to manage a SQL Server instance Policies.
Server Group ServerGroupAdministratorRole
ServerGroupReaderRole
Only members of these database roles can administer and use registered server groups.
SQL Server Agent Fixed Database Roles SQLAgentUserRole
SQLAgentReaderRole
SQLAgentOperatorRole
Only members of one of these database roles can use SQL Server Agent.
Multiserver Environment TargetServersRole Used to work with a Multiserver Environment.
Server Utility UtilityCMRReader
UtilityIMRWriter
UtilityIMRReader
Used to work with the Server Utility.

Below is an image showing all the default database roles for msdb database after a new MSSQL instance has been installed:

Capacity Planning

msdb database disk space requirements depend on what features (e.g. Service Broker and Database Mail) will be used by the MSSQL instance as well the number of jobs executions that will be stored and also the number of backups and restores that will occur in a regular basis. Usually, as more databases the MSSQL instance has, the more disk space will be needed by the msdb database.

Database Mail

When using the SQL Server Database Mail feature, msdb database is used to store copies of the mails and their attachments as also to store the Database Mail event log. To avoid msdb database growing to a non-wanted size, a good solution is to archive old messages. Microsoft has a good article that shows how the archive can be implemented with a SQL Server Agent Job.

Job history

One good way to keep control on the msdb database size is to limit the maximum number of rows for the job history and run a regular purge task. For that in SSMS, right-click on the SQL Server Agent icon and choose Properties:

Select the History page and change the necessary parameters as you desire (for purge purposes configure the “Remove agent history”):

Alternatively the same can be done with T-SQL. Example:

The above is only for a one time only execution. To have the purge running in a regular basis, create a SQL Server agent job and add a job step to run the purge with a dynamic date. The following is an example of keeping only the last 15 days (2 weeks) history in the database:

Operations

Permissions

By default, users do not have access to the msdb database. If you need to provide access to a user in msdb database, is recommended to use the pre-defined database roles already explained on the Roles section in this article.

Note: A user that does not have access to msdb database will not be able to see the SQL Server Agent from the SQL Server Management Studio (SSMS).

Backups

Like any other system database, it is also not recommended to create user objects in the msdb database.

A change in msdb database occurs automatically when one of the following actions occurs:

  • Creation, modification or deletion of a SQL Server Agent job
  • Backup and Restore of any existing database in the MSSQL instance
  • SQL Server native Replication tasks

That is why is recommended to have regular backups of the msdb database since it will be very useful to perform a restore when the msdb database becomes unusable.

Note: The msdb database is by default configured for Simple Recovery Model but if for some reason you decide to change it for a Full Recovery Model then do not forget to add the respective transaction log backup jobs for the msdb Backup Plan.

Move database file locations

Like any regular database, msdb data and transaction log files can be moved to any other location if and when needed to. Below is a T-SQL code example for moving the default data and transaction log files to a new location in S:\SysDB folder:

After running the above command, stop the respective MSSQL instance service and copy the msdb current files to the new location. After the copy finishes successfully, start the respective SQL Server service and verify if there are no issues by checking the database status, the SQL Server Agent service and jobs and if the database mail is being used then perform a simple sending mail test.

How to send a test e-mail in SSMS:

When everything is correctly verified and there are no issues to solve then the old msdb data and transaction log files can be deleted from the old location.

Restrictions

msdb has many restrictions which are described below.

Drop database

The msdb database cannot be deleted. When trying to delete it the respective error that a system database cannot be deleted will be raised:

Set offline

msdb cannot be set to offline. When trying to do it the respective error stating that will be raised:

Change Data Capture (CDC)

Change Data Capture (CDC) cannot be enabled for msdb. When trying to do it the respective error informing that CDC cannot be enabled on a system database will be raised:

Other restrictions

There are some more restrictions that are applied in general to all MSSQL system databases and they are listed below:

  • msdb database and it primary file group cannot be renamed;
  • msdb database primary file group status cannot be set to READ_ONLY;
  • msdb database’s guest user cannot be deleted;
  • msdb database’s default collation is the MSSQL instance collation and cannot be changed;
  • msdb database cannot participate on a database mirroring solution.

Other articles in this series:

See more

To get 3 free licenses to a SQL Server monitoring tool, download ApexSQL Monitor and fill out this simple survey.

References

Vitor Montalvão

Vitor Montalvão

Vitor Montalvão is a senior SQL Server Engineer with more than 20 years of experience working with SQL Server.

He participates in some SQL Server forums, helping other professionals solving SQL Server issues and acting as their mentor whenever is possible.

Vitor also has a website with some useful information about SQL Server: https://f1-sqlserver.wixsite.com/f1-sqlserver

View all posts by Vitor Montalvão
Vitor Montalvão
409 Views