Introduction
This article is the third I am writing about Microsoft SQL system databases.
- The first article Configuration, operations and restrictions of the tempdb SQL Server system database was about the tempdb database
- The second article SQL Server system databases – the master database was about the master database.
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:
|
1 2 3 4 5 6 7 8 |
USE [msdb] GO EXEC msdb.dbo.sp_set_sqlagent_properties @jobhistory_max_rows=1000, @jobhistory_max_rows_per_job=100 GO EXEC msdb.dbo.sp_purge_jobhistory @oldest_date='2017-06-01T00:00:00' |
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:
|
1 2 3 4 5 6 7 8 9 |
USE [msdb] GO DECLARE @OldestDate DATE -- Keep the last 15 days SET @OldestDate = GETDATE() - 15 EXEC MSDB.DBO.SP_PURGE_JOBHISTORY @Oldest_date=@OldestDate |
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:
|
1 2 3 4 5 6 7 |
ALTER DATABASE msdb MODIFY FILE (NAME = MSDBData, FILENAME = 'S:\SysDB\Data\ MSDBData.mdf'); GO ALTER DATABASE msdb MODIFY FILE (NAME = MSDBLog, FILENAME = 'S:\SysDB\Log\ MSDBLog.ldf'); |
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:
- Configuration, operations and restrictions of the tempdb SQL Server system database
- SQL Server system databases – the master database
- SQL Server system databases – the model database
References
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
- An introduction to sp_MSforeachtable; run commands iteratively through all tables in a database - August 18, 2017
- SQL Server system databases – the model database - August 9, 2017
- SQL Server system databases – the msdb database - July 14, 2017