Delete Old History Records from MSDB

If you are performing regular database maintenance tasks, as you should as more than a mere best practice, over long time, the msdb will grow to be very big because of the growth in the history tables that SQL Server maintains, especially the backup history tables. I am talking about 10s of gigabytes. In itself that size is not too big per today's standards and should not be an issue.  However,  these MSDB tables are not optimized out of the box and therefore queries against those tables will become inefficient, I might say very inefficient as these queries may start showing up as some of the worst queries on your sql server.

To solve this problem, you can run the DMV queries to look for missing indexes in MSDB. For example on one of the sql servers I have, the DMV shows missing indexes:

CREATE INDEX [idx_backupset_backup_finish_date_2174848]
       ON [msdb].[dbo].[backupset]
       (
             [type],
             [database_name],
             [backup_finish_date]
       )
             INCLUDE ([backup_size]);

On an out of the box, non-optimized MSDB, typically you would see few more missing indexes recommendations. 

I thought at some point in the past I had come across a script online to create necessary indexes in MSDB. The only one I could find today that looked more comprehensive than others is this one:

MSDB Performance Tuning

---

A second option, or maybe a complimentary option to adding missing indexes in the MSDB, you can also purge/remove old history records from the MSDB. Fortunately SQL Server comes with built-in, system stored procedures you can use to accomplish this. All you need to provide to is how far back history records you want to keep and anything older will be deleted. In the following script you provide the value for the @days parameter and anything older will be purged out of the select history tables from the MSDB.

/*
Using the buil-in system stored procedures, purges history records from MSDB:

- Backup History
- Jobs history
- Mantainance plan logs history
- DB mail history


*/

use msdb
go
-- Add index on backupset table to improve performance
if not exists(select * from sys.indexes where name = 'idx__backupset_backup_finish_date_for_cleanup')
		CREATE NONCLUSTERED INDEX idx__backupset_backup_finish_date_for_cleanup
		ON [dbo].[backupset] ([backup_finish_date])
		INCLUDE ([media_set_id])
go

declare @days int, @delete_before_date datetime

-- Delete history records older than x days
set @days = 365                                      
set @delete_before_date = GETDATE()-@days


print 'Cleaning up backup history....'
exec sp_delete_backuphistory @oldest_date =  @delete_before_date

print 'Cleaning up job history....'
EXECUTE dbo.sp_purge_jobhistory @oldest_date = @delete_before_date

print 'Cleaning up mantainance plan logs history....'
EXEC sp_maintplan_delete_log @oldest_time = @delete_before_date

print 'Cleaning up db mail history....'
exec sysmail_delete_mailitems_sp  @sent_before = @delete_before_date 
exec sysmail_delete_log_sp  @logged_before = @delete_before_date

 

For the job history tables, there is a GUI option as well. Connect to the SQL Server instance in the SSMS then navigate to the SQL Server Agent job, right click then click on the Properties at the bottom in the Pop up menu:

Go to the History page and tweak the values per your needs:

Click OK button at the right-bottom corner to save the changes.