Five facts about the SQL Server MSDB Database


By:   |   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:

msdb 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:

query backupset

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…:

backup database

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

backup database

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:

query backupset

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:

query backup information

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:

msdb system tables

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

create new sql agent job

Then, we are choosing a name for the job:

create new sql agent job

After that, we are creating two job steps:

create new sql agent job

Finally, we schedule the job and click OK:

create new sql agent job

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

run sql job

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:

query job history

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:

script job

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:

sql agent job code

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:

msdb system stored proc code

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:

maintenance plan wizard

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

maintenance plan wizard

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:

msdb maintenance plan 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:

ssis system tables

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:

log shipping tables

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

log shipping stored procs

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:



Last Updated: 2021-04-19


get scripts

next tip button



About the author
MSSQLTips author Sergey Gigoyan 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.

View all my tips
Related Resources



Comments For This Article





download





Recommended Reading

Restoring the SQL Server Master Database Even Without a Backup

Track SQL Server TempDB Space Usage

SQL Server Backup and Restore History Queries

SQL Server TempDB Tutorial

Rebuild System Databases in SQL Server














get free sql tips
agree to terms