SQL Server MSDB Database Facts

By: Sergey Gigoyan | Updated: 2021-04-19 | Comments | Related: More > System Databases

Problem

In this article, we are going to discuss five facts about the SQL Server msdb database, which is one of the five system databases. This system database is used by SQL Server Agent and by some other SQL Server features such as SQL Server Management Studio (SSMS). This material will help gain a better understanding of the msdb database and its use.

Solution

Let’s explore some facts about the msdb database.

Fact 1 – Backup and restore history is stored in the msdb

The msdb contains backup-restore history. So, we can get almost any information about performed backups by querying the corresponding tables. In the SSMS we can find these tables under msdb > Tables > System Tables:

The backupset table, for example, contains information about each backup set (single, successful backup operation). If we haven’t performed any backup on the instance, this table will be empty:

SELECT *
FROM [msdb].[dbo].[backupset]

As we can see, there are no rows in the backupset table:

Now, let’s perform a backup and see how the related information is stored in the tables. To do this, we right-click on a user database on the SSMS, choose Tasks > Back Up…:

Then, we choose the backup type (we have chosen FULL backup) and the name for the backup file:

When the backup is completed, we run the previous query again and can see that the backupset table now returns data. It contains a record for the performed backup:

If we want more detailed information about the backups performed on the instance, we can use more complex queries by joining the backup-related tables. For instance, the query below returns the database name, backup file name and path, backup type, size, start and finish dates for each backup successfully completed on the instance:

USE msdb
GO
 
SELECT bs.database_name, bmf.physical_device_name, 
       CASE bs.type WHEN 'D' THEN 'FULL'
                    WHEN 'I' THEN 'DIFFERENTIAL'
                    WHEN 'L' THEN 'TRNSACTION LOG'
                    ELSE bs.type
            END AS BackupType,
       bs.backup_size AS BackupSizeInBytes,
       bs.backup_start_date, backup_finish_date
FROM   
backupmediafamily bmf
INNER JOIN
msdb.dbo.backupset bs
ON bmf.media_set_id = bs.media_set_id

As we can see, in our case we have only one backup that was done on our instance:

It is worth mentioning that the backup-restore information stored in the msdb is used by SSMS to perform restore operations.

Fact 2 – The msdb database contains SQL Server Agent Job related information

The information about SQL Server Agent Jobs is also stored in the msdb database. For example, by using the query below, we can retrieve some details about the jobs on the current instance:

USE msdb
GO
 
SELECT j.name,
       jh.run_date,
       jh.step_name,
       jh.run_time,
       jh.run_duration
FROM 
sysjobs j
INNER JOIN 
sysjobhistory jh
ON j.job_id = jh.job_id

We use sysjobs and sysjobhistorytables of the msdb database to see job execution history in the instance. The result is empty, which means that there is no job history yet:

Now, we will create a new job and run it. First, under SQL Server Agent, we choose Jobs > New Job…:

Then, we are choosing a name for the job:

After that, we are creating two job steps:

Finally, we schedule the job and click OK:

To execute the job, we locate it under SQL Server Agent, right-click on it and choose Start Job at Step… and click Start:

Hence, we have a job executed on our instance and, therefore, job history. If we run the previous query again, we can see how these tables are filled with the corresponding data:

Fact 3 – The msdb database contains stored procedures for creating Jobs, Steps, Schedules, etc.

Above, we created a job using the SSMS visual interface. Jobs can also be created by T-SQL code, using the corresponding stored procedures. These procedures are stored in the msdb database. To better understand which procedures we are talking about, let’s generate the creation script of our job created above. We right-click on the job name, choose Script Job as > CREATE To > New Query Editor Window:

On the new query window, we can see the full script which was used for creating the job. To easily find and highlight the stored procedures of the msdb database used in the script, we just type msdb in the search box:

Thus, we can see that the sp_add_job and sp_add_jobstep stored procedures of the msdb database are used to create jobs and job steps. We can find the code of these stored procedures by locating them under the msdb database’s Programmability > Stored Procedures > System Stored Procedures and by right-clicking on one of them and choosing Modify:

These procedures are just two examples of job-related stored procedures. There are other procedures for working with jobs in msdb such as procedures for deleting jobs and job steps, getting SQL Server Agent job-related information, and so on.

Fact 4 – The msdb database contains Maintenance plans-related information but not in "sysdbmaintplan_" tables

Maintenance-plan related information is stored in the msdb database. Let’s create a maintenance plan and see where the related information is stored. In SSMS, we create a new maintenance plan using the Maintenance Plan Wizard:

We choose a Backup Database (Full) Task as a sample and create a backup database task for two databases:

After having our maintenance plan created, let’s locate the corresponding information in msdb. When we expand "System Tables" under the msdb database, we can see some tables starting with "sysdbmaintplan_". This can be confusing as on the one hand, the name suggests that these tables should contain maintenance-plan related data, but on the other hand, when we query these tables, they are empty:

USE msdb
GO
 
SELECT * FROM sysdbmaintplans
 
SELECT * FROM sysdbmaintplan_databases
 
SELECT * FROM sysdbmaintplan_jobs
 
SELECT * FROM sysdbmaintplan_history

Even if we have executed the maintenance plan, we can see that there is no related info in these tables:

This is because starting with SQL Server 2005, the data in these tables is not changed and they exist just for keeping the existing information in case of upgrading from older versions. According to Microsoft, the above-mentioned tables will be depreciated in future versions of SQL Server. The information about the maintenance plans, however, can be found in the SSIS package-related tables. As any maintenance plan creates an SSIS package that is run by the SQL Server Agent job, if we query the sysssispackages and sysssispackagefolders tables in the msdb database, we can find information about our maintenance plan there:

 USE msdb
GO 
 
SELECT * FROM sysssispackages
 
SELECT * FROM sysssispackagefolders

In the result set, the above-created maintenance plan-related information is highlighted in red:

Fact 5 – The msdb database contains Log Shipping-related information

Log-shipping related information and stored procedures are also stored in the msdb database. If we filter the system tables of the msdb database using the "log_shipping" keyword, we can see the log shipping-related tables:

If we use the same keyword in the system stored procedures, we can find the procedures of configuring and monitoring log shipping:

Conclusion

To sum up, in this article, we discovered some important features of the msdb database. Particularly, we have seen that the backup-restore related information as well as SQL Server Agent Jobs, Maintenance Plans and Log Shipping related information is stored in msdb. Additionally, in the msdb’s system procedures, there are stored procedures for implementing and monitoring different database tasks such as creating SQL Server Agent Jobs or configuring Log Shipping.

Next Steps

For more information, please follow the links bellow:

About the author

Sergey Gigoyan is a database professional with more than 10 years of experience, with a focus on database design, development, performance tuning, optimization, high availability, BI and DW design.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2021-04-19

Five facts about the SQL Server MSDB Database