Rajendra Gupta
Maintenance Plan History

MSDB SQL Database Maintenance and Cleanup

October 4, 2021 by

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.

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.

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

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.

Cleanup History task

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

Remove History data older than

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.

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

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

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.

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.

SQL Server agent job history in SQL Database

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.

Remove Agent History

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.

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

Maintenance Plan History

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:

    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:

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

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.

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.

Cleanup execution history

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.

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.

Rajendra Gupta
Maintenance, Performance

About Rajendra Gupta

As an MCSA certified and Microsoft Certified Trainer in Gurgaon, India, with 13 years of experience, Rajendra works for a variety of large companies focusing on performance optimization, monitoring, high availability, and disaster recovery strategies and implementation. He is the author of hundreds of authoritative articles on SQL Server, Azure, MySQL, Linux, Power BI, Performance tuning, AWS/Amazon RDS, Git, and related technologies that have been viewed by over 10m readers to date. He is 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 his contribution to the SQL Server community, he has been recognized with various awards including the prestigious “Best author of the year" continuously in 2020 and 2021 at SQLShack. Raj is always interested in new challenges so if you need consulting help on any subject covered in his writings, he can be reached at rajendra.gupta16@gmail.com View all posts by Rajendra Gupta

1,841 Views