Tuesday, September 19, 2017

MSDB Maintenance and Cleanup

The MSDB database is a system db that stores info related to the SQL Server agent (jobs, operators, backup history, broker, db mail, etc..)

It is important to give it the proper maintenance, (include it as with the other system databases in your backup policies). And for this post, we will cover the history cleanup.

Since MSDB stores backup and job history, if you don't perform a cleanup task in a regular basis, your database can grow and occupy a lot of unnecessary storage. Another drawback is that the backup operations can take longer because the required inserts on very large history tables.

The creation of a cleanup task is very simple, and will not take more than 10 minutes and will reduce the risk of issues in a future, so i strongly recommend to do it in your servers now.

The simplest way to do it, is running the maintenance plan wizard, and select the "cleanup history" option.
Using the wizard to cleanup MSDB

After that, just check all the options you want and choose the data retention that best suit your policies, select the job schedule and you are done!
Select the options you want and finish the wizard.

If you have a lot of servers, you can also create a custom script to deploy it on all your instances using the stored procedures:

sp_delete_backuphistory
sp_purge_jobhistory 


Sources:

https://docs.microsoft.com/en-us/sql/relational-databases/databases/msdb-database

https://docs.microsoft.com/en-us/sql/integration-services/control-flow/history-cleanup-task

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-delete-backuphistory-transact-sql

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-purge-jobhistory-transact-sql

No comments:

Post a Comment