MSDB SQL Database Maintenance and Cleanup

This article explores the MSDB SQL Database maintenance and cleanup process essential for every DBA.

Introduction

SQL Server system database – MSDB stores SQL Server agent jobs, History, schedules, database mail, service broker, backup, and restore History. In many databases, we run routine jobs, backups, maintenance plans, and due to these things, MSDB size might be cumbersome if you do not perform the regular cleanup. By default, SQL Server does not purge data from the history tables.

Sometimes, you get an error message while trying to view the job history.

Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

The error might be due to huge tables in the MSDB database. Therefore, it is required to perform regular maintenance of the MSDB database.

MSDB SQL Database properties

By default, MSDB database has a primary(MSDBData.MDF) and log (MSDBLog.ldf) transaction log file.
Data and log file Auto growth are set to 10%
The MSDB database is in the Simple recovery model. However, you can convert it to a full recovery model(recommended) and configure transaction log backups. This way, you can restore the MSDB database after the server crash and obtain the latest backup and restore History

Cleanup MSDB SQL Database

As stated earlier, the MSDB stores information related to database backup, SQL agent jobs, schedules, database mails, etc. Therefore, let’s go through the process to clean them regularly.

Cleanup Backup and Restore History

The MSDB database stores database backup and restore History in the following tables.

We can join multiple tables for retrieving the backup information. For example, the following T-SQL gives backup start date, end date, backup type, size for all databases in the instance.

SELECT

CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,

msdb.dbo.backupset.database_name,

msdb.dbo.backupset.backup_start_date,

msdb.dbo.backupset.backup_finish_date,

CASE msdb..backupset.type

WHEN 'D' THEN 'Database'

WHEN 'L' THEN 'Log'

When 'I' THEN 'Differential database'

END AS backup_type,

msdb.dbo.backupset.backup_size,

msdb.dbo.backupmediafamily.physical_device_name,

msdb.dbo.backupset.name AS backupset_name

FROM msdb.dbo.backupmediafamily

INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id

WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE()-1 )

ORDER BY

msdb.dbo.backupset.backup_finish_date desc

To clean up database backup and restore tables history, we use the sp_delete_backuphistory stored procedure. This procedure requires the oldest date retained in the backup and restore tables.

1	sp_delete_backuphistory [ @oldest_date = ] 'oldest_date'

The following script deletes the records older than 1st January 2021 from the backup and restores history tables.

USE msdb;

EXEC sp_delete_backuphistory @oldest_date = 01/01/2020;

You could use the database maintenance plan – Cleanup History task and configure it to clean up backup tables automatically.

Select the Cleanup History task in the maintenance plan and click Next.

On the next page, select the value for the “Remove historical data older than” parameter.

SQL Server Agent Job History

SQL Server stores all job executions in the MSDB SQL Database tables. Apart from the application or database-specific jobs, we have jobs for full backup, transaction log backup, index maintenance, Statistics update, Index consistency check, etc. Therefore, over time, these tables become huge. However, in typical cases, we do not require much older data to review job execution.

Therefore, you can use sp_purge_jobhistory stored procedure for job history cleanup. It has following syntax.

You can specify the job name or Job id along with the history cleanup date.

sp_purge_jobhistory

{ [ @job_name = ] 'job_name' |

| [ @job_id = ] job_id }

[ , [ @oldest_date = ] oldest_date ]

The following script removes all history for the specified job name.

USE msdb ;

EXEC dbo.sp_purge_jobhistory

@job_name = N'DBLogBackups' ;

Similarly, the below script removes history older than 7 days for the specified job.

DECLARE @Date datetime

SET @Date = DATEADD(dd,-7,GETDATE())

EXECUTE dbo.sp_purge_jobhistory @oldest_date = @Date,

@job_name = N'DBLogBackups' ;

If you do not specify any job name or ID, it removes History for all jobs from the SQL instance per the specified cleanup date.

USE msdb ;

EXEC dbo.sp_purge_jobhistory

Similar to the database backup and restore history cleanup, you can configure a maintenance plan for SQL Server Agent Job History cleanup, as shown below.

You can also configure SQL Server agent property and configure history removal at the instance level. Right-click on SQL Server Agent and go to History. Here, put a tick on Remove agent history and specify the cleanup date.

Maintenance Plan History

SQL Server also maintains the logs for the maintenance plan tasks and executions. It uses the following tables for storing the maintenance plan logs.

msdb.dbo.sysmaintplan_log
msdb.dbo.sysmaintplan_logdetails

To clean up these tables, use the stored procedure sp_maintplan_delete_log and specify the maintenance plan ID, subplan ID and the cleanup date.

1	EXEC sp_maintplan_delete_log @plan_id, @subplan_id, @oldest_time

Alternatively, configure the Maintenance plan history task in the history cleanup task as shown below.

SQL Database mail history cleanup

If you use the database mails for agent job notification or send results to users, the MSDB can be huge. The MSDB database uses tables sysmail_mailitems, sysmail_log, sysmail_attachments tables for storing database e-mails. Therefore, you should include database mail history cleanup as well for the MSDB maintenance task.

To clean up MSDB database mails, we can use the following stored procedures.

sysmail_delete_mailitems_sp: It deletes e-mail messages permanently from the Database Mail internal tables. This SP has the following syntax:

1
2

sysmail_delete_mailitems_sp [ [ @sent_before = ] 'sent_before' ]
[ , [ @sent_status = ] 'sent_status' ]

In the @sent_status column, you can specify the values sent, unsent, retrying, and failed
sysmail_delete_log_sp: It deletes all database mail logs. In the @event_type, you can specify Success, Warning, Error or Information values:

1
2

sysmail_delete_log_sp [ [ @logged_before = ] 'logged_before' ]
[, [ @event_type = ] 'event_type' ]

The following script clears database mail logs older than seven days from the internal tables using stored procedures specified above.

DECLARE @Date datetime

SET @Date = DATEADD(dd,-7,GETDATE())

EXECUTE msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @Date ;

EXECUTE msdb.dbo.sysmail_delete_log_sp @logged_before = @Date ;

Clean Suspect_pages Table

SQL Server marks the pages as Suspect when it encounters the following errors in accessing a data page:

Error 823 due to cyclic redundancy check (CRC) caused by the hardware (disk) errors
Error 824 such as logical error or torn page detection

The MSDB SQL Database contains one row per page failed with the errors 823 and 824 in the suspect_pages table. As per Microsoft documentation, DBA is responsible for managing the table. Therefore, you can clean it by deleting the old rows. You should periodically delete rows using the delete statement below. It deletes the restored, repaired, or the deallocated pages.

DELETE FROM msdb..suspect_pages

WHERE (event_type = 4 OR event_type = 5 OR event_type = 7);

The event_type column defines the type of error:

1: 823 error
2: Bad checksum
3: Torn page detection
4: Restored after page was marked bad
5: Repaired page using DBCC
7: Deallocated

Cleanup execution history of Policy-based Management

SQL Server 2008 onwards Policy-Based Management feature is available. It evaluates the policies against a database or instance. For example, To enforce xp_cmdshell should be disabled on an instance, we s can use it. SQL Server stores the results of policy execution in the MSDB SQL Database. This table might be huge in MSDB.

Therefore, SQL Servers has a job created – syspolicy_purge_history in each instance during SQL Server setup. The job purge data older than the days defined in HistoryRetentionInDays property of Policy Management.

Alternatively, you can run the stored procedure mentioned below to clear the execution history.

1	EXEC msdb.dbo.sp_syspolicy_create_purge_job

Log Shipping History

We can configure log shipping in SQL Server that works on backup and restore method. It can prepare a standby or DR copy of a SQL Database by applying continuous transaction log backup from primary to secondary.

The log shipping stores information in the MSDB database tables – log_shipping_monitor_history_detail and log_shipping_monitor_error_detail

SQL Server automatically cleans up these tables using the log shipping backup and restore jobs. It uses seven days of data retention. You should monitor the log shipping jobs and the history table sizes to avoid the significant growth of MSDB. Alternatively, you can run the stored procedure sp_cleanup_log_shipping_history and clean up the table listed above.

Conclusion

The MSDB SQL Database is an essential and valuable database for various functions in SQL Server. SQL Server stores many logs, history data in the system Database MSDB. Therefore, you should perform regular database maintenance and clean the old historical data.

Author
Recent Posts

Rajendra Gupta

Hi! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related issues, and Performance Tuning with over 14 years of experience.

I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.

I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.

Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.

Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta

Latest posts by Rajendra Gupta (see all)

Understanding PostgreSQL SUBSTRING function - September 21, 2024
How to install PostgreSQL on Ubuntu - July 13, 2023
How to use the CROSSTAB function in PostgreSQL - February 17, 2023