Delete old entries in the backup and restore history tables in msdb database

The size of msdb database in SQL Server can increase over time because additional rows are added to the backup and restore history tables after each backup or restore operation is performed.
To delete old entries in the backup and restore history tables in msdb database, you can use a stored procedure sp_delete_backuphistory.
It deletes the entries for backup sets older than the specified date, and reduces the size of the backup and restore history tables in msdb database, and it must be run from the msdb database.

The syntax for using this stored procedure is:

sp_delete_backuphistory [ @oldest_date = ] 'oldest_date'

Here is an example that deletes all entries that are older than July 20, 2012, 12:00 A.M. in the backup and restore history tables:

USE msdb;
GO
EXEC sp_delete_backuphistory @oldest_date = '07/20/2012';

To delete all entries that are older than 30 days:

USE [msdb]
GO
DECLARE @Delete_Date [datetime]
SET @Delete_Date = GETDATE() - 30
EXEC sp_delete_backuphistory @Delete_Date
GO

To delete all information about the specified database from the backup and restore history tables (for example AdventureWorks database) you can use sp_delete_database_backuphistory stored procedure:

USE msdb;
GO
EXEC sp_delete_database_backuphistory @database_name = 'AdventureWorks';

If you need to delete a lot of data in history tables, use smaller batches to minimize contention on the msdb database tables.