SQL Server MSDB Database Tables

By: Eric Blinn

Overview

There are many objects that make up the MSDB database and being able to query some of these tables directly can be very helpful. We will look at some of the tables that are useful to query.

What are the key tables in the MSDB database that are important for SQL Server Professionals to know about?

There are a large number of tables in MSDB that a SQL Server professional should know. This author has written a few MSSQLTips specifically about them. This tip is all about the SQL Server Agent tables and this tip covers the backup and restore history tables.

This tutorial will highlight a few groups of MSDB tables that are commonly queried.

SELECT * FROM dbo.suspect_pages;

Suspect_pages helps track corrupt data pages or those with the potential for corruption due to a failing disk subsystem.  These could be pages that were found due to normal querying that had an 823 or 824 error, were found by a DBCC Check command, or were found during a backup checksum validation.  DBAs should be aware of this table and query it occasionally to be sure make sure there are no uncorrected errors.

There is a limit of 1000 rows for this table.  Hopefully that is not a number that is commonly tested, but if it is older rows are not aged out automatically to make room for new ones.  SQL Server simply stops recording information if there are 1000 rows in the table.  Because of this, older rows should be deleted by a DBA after reviewing and resolving what caused them.  Read more about this table in this tip!

SELECT backup_set_id, database_name, bset.media_set_id, position, family_sequence_number, physical_device_name
FROM msdb.dbo.backupset bset
  INNER JOIN backupmediafamily bfam ON bset.media_set_id = bfam.media_set_id;

The table backupset contains a row every time a backup is taken. The table backupmediafamily contains one row for every backup file created or appended. Most DBAs back up one database per file and leave these tables with a 1:1 relationship. If using striped backups or stuffing multiple backups into a single file, then they will not be 1:1.

Consider this result set from the above query from a striped backup.

There are 2 rows, but only a single backup set id, number 1. This indicates there is only one backup.  The backup was sent to the same media set id, 1, but that set contains 2 sequence numbers. This means that to complete a restore of the database testtde both files will be needed and must be listed.

RESTORE HEADERONLY FROM 
DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\Stripe1.bak',
DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\Stripe2.bak';

Now consider this result set for a series of backups sharing a single file.

There are 2 distinct backup sets, 2 and 3, but a single media set and single file name. This indicates that there are multiple backups sharing a single file. Both rows have a sequence of 1 indicating that there is no striping for either backup.  In this case, the position column becomes important.  "FILE" 1 will be another testtde backup while "FILE" 2 will be a MSDB backup.

RESTORE HEADERONLY FROM 
DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\MultiBackupFile.bak'
WITHH FILE = 1;

RESTORE HEADERONLY FROM 
DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\MultiBackupFile.bak'
WITH FILE = 2;

SELECT * FROM [msdb].[dbo].[sysmail_log];

This table records all dbmail related events.  It is the same information that can be found in SSMS by clicking the "View Database Mail Log" context menu option.

SELECT sp.profile_id, sp.name, spa.account_id, sa.name, sa.email_address, sa.display_name, ISNULL(spp.is_default, 0) IsDefaultProfile
FROM [dbo].[sysmail_profile] sp
  INNER JOIN [dbo].[sysmail_profileaccount] spa ON sp.profile_id = spa.profile_id
  INNER JOIN [dbo].[sysmail_account] sa ON spa.account_id = sa.account_id
  LEFT OUTER JOIN [dbo].[sysmail_principalprofile] spp ON sp.profile_id = spp.profile_id;

This group of tables record the DBMail profile and account settings.  The table sysmail_profile contains one row per profile while sysmail_account contains a row for each account.  Each row in the table sysmail_profileaccount matches 1 profile to 1 account.  This allows for a many-to-many relationship between profiles and accounts.

This example output shows 2 profiles sharing a single account.

Can I see the code for the system tables and views in MSDB and if so, how?

The system tables in MSDB can be viewed using sp_help or using the "Design" context menu option in the Object Explorer.

exec sp_help sysjobs;

The views do not have a modify or "script as" option in their context menu as user views do, but they can still be viewed using sp_helptext.

exec sp_helptext sysjobs_view;

Can I change these objects in the MSDB database?

SQL Server will allow the system tables and views to be changed, but this should absolutely not be done. The wizards, SSMS screens, and Azure Data Studio screens expect these objects to behave a certain way and changing them could cause those to fail.

An exception might be made for non-clustered indexes. On an instance with many databases and lots of backup history or many SQL Server Agent jobs with a large amount of history, some monitoring queries can end up running prohibitively slow and adding non-clustered indexes to support reporting or other operations can solve that problem.

Can I store my own objects in the MSDB database?

SQL Server will allow objects to be added to the MSDB database, but that should probably not be allowed.  Instead, keep a special user database with a name like "_DBA" where administrators can keep objects that are used to manage the instance.