MSDB Maintenance: Your Ultimate Guide to SQL Server System Database Management

In the previous articles of the SQL Server System Databases series, we have gone through the System Databases that are installed by default during SQL Server Installation, understood the purpose of each of those system databases and explored the Tempdb database and its maintenance in more detail. In this article, we will explore the MSDB database in more detail along with the frequently faced issues around MSDB database and how to resolve them the right way.

Table of Contents

• The MSDB Database
• Frequently Faced Issues in MSDB Database
• Backup History
• SQL Server Agent Jobs History
• Maintenance Plans
• SQL Server Database Mail History
• SSIS Packages
• The Bottom Line
• Conclusion

The MSDB Database

The MSDB SQL Server system database stores all critical configuration information and historical information related to SQL Server Agent Service, SQL Server Service Broker, Database Mail, Log Shipping, Database Mirroring, etc.:

• SQL Server Agent Service
  • SQL Server Agent Jobs – Configuration data and History details
  • SQL Server Agent Alerts – Configuration data
  • SQL Server Agent Operators – Configuration data
  • SQL Server Agent Proxies – Configuration data
  • Related information
• SQL Server Database Mail, including Service Broker – Configuration data and historical Mail log details.
• SQL Server Backup and Restore details – History data of all Database Backup and Restore events happening in the instance of SQL Server.
• Maintenance plans, SSIS Packages, and related information – Configuration data, related data, and the data on execution of all these items via SQL Server Agent Jobs.
• Log Shipping Configurations, Replication Agent Profiles, Data Collector Jobs – Configuration data of all the mentioned High Availability techniques.

Whenever any of the above critical configurations is modified, it is recommended to take a Full backup of the MSDB database to avoid any data loss if a failure happens.

Even though SQL Server Agent Service stores critical configuration details across tables in the MSDB database, SQL Server stores few configuration details in Windows Registry as well. For that, it uses the extended Stored Procedure named sp_set_sqlagent_properties.

Let’s have a quick look into the Registry location where SQL Server stores SQL Server Agent Service configurations. Important: This is for learning purposes only, and we don’t recommend changing any configuration values. Otherwise, it might end up in weird errors related to SQL Server Agent Service.

Open the Registry Editor by typing regedit in the command prompt:

Click Enter to open the Registry Editor:

Now, navigate to the path:

Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL13.MSSQLSERVER\SQLServerAgent

View the below configuration details. The marked fragment refers to the SQL Server instance name, and it might vary in your environment based upon the SQL Server version and instance name.

A quick look on the registry indicates that there are certain parameters related to SQL Server Agent Service stored. Since we don’t recommend changing any parameters related to the SQL Server Agent Service and shared above only for learning purposes, we won’t dive deep into it here.

However, if you intend to change any of the SQL Server Agent Service properties to meet Business or Production requirements, then you can modify it by Right-clicking on the SQL Server Agent Service and choose Properties as shown below.

Even though there are lot of parameters available related to SQL Server Agent Service and the scope of this article is related to msdb database, I’ve excluded those and covering only the options specific to msdb database by clicking on History menu as shown below where we can configure the size of Job History Logs and Agent History.

Frequently Faced Issues in MSDB Database

In any production instance of SQL Server, we will have a lot of SQL Server Agent Jobs, Database Mails, Maintenance plans, and Full/Transactional Log backups enabled. Depending upon the no. of databases in the instance or the no. of SQL Server Agent jobs available, or Database Mail usage, our SQL Server will start logging the history information of all enabled features, thereby increasing the size of the MSDB database. If not properly maintained, this will affect the MSDB database performance and operations related to that.

Let us review the features discussed earlier and the tables used to store history data to understand how we can keep the size of those tables under control.

• Backup History
• SQL Server Agent Jobs History
• Maintenance Plans
• SQL Server Database Mail History
• SSIS Packages

To find out which tables in the MSDB database take more space, we can use the Disk Usage by Top Tables Reports which comes as part of SQL Server default reporting in SQL Server Management Studio.

Open SSMS and right-click on MSDB database > Reports > Standard Reports > Disk Usage by Top Tables to generate the report of tables sorted by Disk Usage:

Click Disk Usage by Top Tables to view the report. As my instance is a development one, there aren’t huge tables, but this report can show the size of all tables in a database sorted in descending order.

We can also use the below query to get the tables’ sizes within a database.

SELECT -- TOP(10)
	  SCHEMA_NAME(o.[schema_id]) Schema_name
	, o.name object_name
    , total_size = CAST(SUM(au.total_pages) * 8. / 1024 AS DECIMAL(18,2))
    , total_rows = SUM(CASE WHEN i.index_id IN (0, 1) AND au.[type] = 1 THEN p.[rows] END)
FROM sys.objects o 
JOIN sys.indexes i ON o.[object_id] = i.[object_id]
JOIN sys.partitions p ON i.[object_id] = p.[object_id] AND i.index_id = p.index_id
JOIN sys.allocation_units au ON p.[partition_id] = au.container_id
WHERE i.is_disabled = 0
AND i.is_hypothetical = 0
AND o.Type in ('S','U','V')
GROUP BY o.name, SCHEMA_NAME(o.[schema_id])
ORDER BY 3 DESC

Once we know which tables take more space, we can use the related stored procedures to keep their size under control.

Backup History

The DBA’s primary responsibility is to ensure that Full Backups and Transactional Logs are enabled across all Production SQL Server instances to recover the databases to a point in time.

SQL Server stores the Backup details and the Restore information in the following MSDB database tables:

• backupfile
• backupfilegroup
• backupmediafamily
• backupmediaset
• backupset
• restorefile
• restorefilegroup
• restorehistory

For a significant no. of databases in the SQL Server instance configured with Full backups and Transactional Log backups, records across the above tables can increase faster.

Thus, SQL Server provides two system stored procedures in the MSDB database to control the size of the above tables:

• sp_delete_backuphistory – deletes the backup history data across the above 8 tables based upon the oldest date parameter.
• sp_delete_database_backuphistory – deletes the backup history data across the above 8 tables based upon the database name.

The syntax for executing the above system stored procedures:

exec msdb.dbo.sp_delete_backuphistory @oldest_date = 'oldest_date'

exec msdb.dbo.sp_delete_database_backuphistory @database_name = 'database_name'

When we execute any of the stored procedures described above on a database containing huge records across the backup history tables, we might get blocking or notice that the records are deleted very slowly. To resolve this, we create the below missing index on the backupset table. It can be identified via the execution plan of the stored procedure to execute any of our stored procedures faster.

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE OBJECT_ID = OBJECT_ID('[dbo].[backupset]') AND name = 'IX_BackupSet_FinDate_MediaSet')
CREATE NONCLUSTERED INDEX IX_BackupSet_FinDate_MediaSet ON backupset(backup_finish_date) 
INCLUDE (media_set_id)
GO

SQL Server Agent Jobs History

SQL Server stores all the SQL Server Agent Jobs history in the msdb.dbo.sysjobhistory table. Also, SQL Server has a system stored procedure named msdb.dbo.sp_purge_jobhistory that helps to keep the sysjobhistory table size under control.

The syntax to run the sp_purge_jobhistory stored procedure will be:

exec msdb.dbo.sp_purge_jobhistory @job_name = 'job_name', @job_id = 'job_id', @oldest_date ='oldest_date'

All 3 parameters are optional, and we’d recommend to execute the above procedure by passing the oldest_date parameter to keep the sysjobhistory table size under control.

Maintenance Plans

SQL Server stores the details of all Maintenance plans in the below tables:

• msdb.dbo.sysmaintplan_log
• msdb.dbo.sysmaintplan_logdetail

SQL Server has a built-in stored procedure named msdb.dbo.sp_maintplan_delete_log to keep the sizes of these 2 tables under control.

The syntax to execute the procedure will be:

exec msdb.dbo.sp_maintplan_delete_log @plan_id = '', @subplan_id = '', @oldest_Time = 'oldest_datetime'

All 3 parameters are optional. We’d recommend executing the above procedure, passing the oldest_time parameter to keep the size of the above two tables under control.

SQL Server Database Mail History

SQL Server stores all Database Mail history logs across the below tables:

• sysmail_mailitems
• sysmail_log
• sysmail_attachments
• sysmail_attachments_transfer

To keep these history table sizes under control, SQL Server offers 2 system Stored Procedures named msdb.dbo.sysmail_delete_mailitems_sp and msdb.dbo.sysmail_delete_log_sp.

The syntax to execute these stored procedures will be:

exec msdb.dbo.sysmail_delete_mailitems_sp @sent_before = 'oldest_datetime', @sent_status = NULL

exec msdb.dbo.sysmail_delete_log_sp @logged_before = 'oldest_datetime', @event_type = NULL

For both procedures, all parameters are optional. However, it is recommended to use the sent_before or logged_before parameters to delete the older records based upon the retention period.

In few scenarios, if all tables related to Database Mail are huge, running the delete procedure will run forever. A quicker way to handle the problem is to delete the Foreign key constraint on sysmail_attachments and sysmail_send_retries tables, truncate the above 4 tables and recreate the 2 foreign keys back on sysmail_attachments and sysmail_send_retries tables as shown below:

USE MSDB;

ALTER TABLE [dbo].[sysmail_attachments] DROP [FK_sysmail_mailitems_mailitem_id];
GO
ALTER TABLE [dbo].[sysmail_send_retries] DROP [FK_mailitems_mailitem_id];
GO

TRUNCATE TABLE [dbo].[sysmail_attachments];
TRUNCATE TABLE [dbo].[sysmail_send_retries];
TRUNCATE TABLE [dbo].[sysmail_mailitems];
TRUNCATE TABLE [dbo].[sysmail_log];

ALTER TABLE [dbo].[sysmail_attachments]  WITH CHECK ADD  CONSTRAINT [FK_sysmail_mailitems_mailitem_id] FOREIGN KEY([mailitem_id])
REFERENCES [dbo].[sysmail_mailitems] ([mailitem_id])
ON DELETE CASCADE;
ALTER TABLE [dbo].[sysmail_attachments] CHECK CONSTRAINT [FK_sysmail_mailitems_mailitem_id];
GO

ALTER TABLE [dbo].[sysmail_send_retries]  WITH CHECK ADD  CONSTRAINT [FK_mailitems_mailitem_id] FOREIGN KEY([mailitem_id])
REFERENCES [dbo].[sysmail_mailitems] ([mailitem_id])
ON DELETE CASCADE;
ALTER TABLE [dbo].[sysmail_send_retries] CHECK CONSTRAINT [FK_mailitems_mailitem_id];
GO

SSIS Packages

SQL Server stores all SSIS(*.dtsx) packages in the msdb.dbo.sysssispackages table. This table is a configuration table, however, in random cases, chances are that there might be a lot of SSIS packages dumped upon the table. It causes the size of this table to grow huge.

In those cases, we need to identify whether there are any unwanted packages and delete those packages to keep the sysssispackages table size under control.

The Bottom Line

SQL Server doesn’t have built-in jobs to handle the task of deleting across all tables discussed above. Still, we have the oldest date parameter available for all the above procedures.

Hence, the recommended approach to handle MSDB table size under control would be to define a retention period based upon the number of days, and create a new SQL Server Agent job to execute the below script on a scheduled basis:

declare @retention_date datetime = '2021-04-01'
exec msdb.dbo.sp_delete_backuphistory @oldest_date = @retention_date;
exec msdb.dbo.sp_purge_jobhistory @oldest_date = @retention_date;
exec msdb.dbo.sp_maintplan_delete_log @oldest_Time = @retention_date;
exec msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @retention_date;
exec msdb.dbo.sysmail_delete_log_sp @logged_before = @retention_date;

Conclusion

We have learnt about the list of tables that can grow faster in the MSDB database and how to keep the size of these tables under control. We have derived a handy script with the list of procedures to execute regularly to prevent the MSDB database growing to huge size as well. Hope that this article will be helpful for your automation and this information will free your mind from MSDB database maintenances and concentrate on other activities.