Keeping MSDB System Database Lean and Fit

The system stored procedure sp_delete_backuphistory reduces the size of the backup and restore history tables by deleting the entries for backup sets older than the specified date.

Additional rows are added to the backup and restore history tables after each backup or restore operation is performed; therefore, we recommend that you periodically execute sp_delete_backuphistory.

The tables within msdb that hold this history include:

USE msdb
GO

SELECT * FROM backupfile
SELECT * FROM backupfilegroup
SELECT * FROM backupmediafamily
SELECT * FROM backupmediaset
SELECT * FROM backupset
SELECT * FROM restorefile
SELECT * FROM restorefilegroup
SELECT * FROM restorehistory

A simple script to execute to purge this history older than 30 days:

USE msdb
GO

DECLARE @CutOffDate DATETIME = DATEADD(dd, -30, GETDATE())
EXEC sp_delete_backuphistory @CutOffDate

Trovato qui.

Pagina MSDN.