Best Practices to maintain MSDB

1. sp_delete_backuphistory. This stored procedure deletes backup history. It will be run once per week, and configured to keep 30 days worth of history. Ola Hallengren (OH), our best practices suite, already has created a job, we simply need to schedule it.

2. sp_purge_job_history. This stored procedure deletes job history. It will be run once per week, and configured to keep 30 days worth of history.  OH already has created a job, we simply need to schedule it.

3. db_mail_cleanup. A job will be created to run the attached SQL ("db_mail_cleanup.txt"). It deletes emails sent by SQL Server after 30 days. 

db_mail_cleanup

-- https://blog.devart.com/reduce-msdb-big-size.html

DECLARE @DateBefore DATETIME 

SET @DateBefore = DATEADD(DAY, -30, GETDATE())

EXEC msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @DateBefore --, @sent_status = 'sent'

EXEC msdb.dbo.sysmail_delete_log_sp @logged_before = @DateBefore

Justification

It contains backup history, job history and 'sent' mail items (by the built-in email client). Also, job history is stored in this database. Mail sent by the database is also stored in this database.

Having so much historical information is unnecessary, wastes storage and ultimately slows down backups (since new information will take longer to be inserted).

Backups (outside of 30 days) can be restored without having a history in msdb. Beyond 30 days, backup history, job history and email history has almost zero utility.

It is a best practice / recommendation to maintain msdb regularly by deleting unneeded job history, backup history and sent emails.

Implementation Plan

1.  enable a schedule for sp_delete_backuphistory to run at 21:00 on Fridays.

2.  enable a schedule for sp_purge_job_history to run at 21:30 on Fridays.

3. create a new job with the script for db_mail_cleanup, and create a schedule for it to run on 21:45 on Fridays.