If you have suddenly discovered that your msdb database is gigabytes large due to the storage of backup/restore history, you may be tempted to use sp_delete_backuphistory to trim down the history. Do not be tempted! sp_delete_backuphistory is poorly written and will often take days to finish if you have not or seldom maintain your backup history. After seeing logs indicating that sp_delete_backuphistory had run for 100+ hours before ultimately failing, I put together a backup history maintenance script that cleaned up the million plus rows in each of the backup history tables in just over an hour. Also, feel free to try the other alternative sp_delete_backuphistory script that I found.

declare @DaysToRetain INT
set @DaysToRetain = 7

SET NOCOUNT ON

--delete restorefile
delete restorefile from restorehistory rh
join restorefile rf on rh.restore_history_id = rh.restore_history_id
where rh.restore_date < GetDate()- @DaysToRetain

--delete restorefilegroup
delete restorefilegroup from restorehistory rh
join restorefilegroup rfg on rfg.restore_history_id = rh.restore_history_id
where rh.restore_date < GetDate()- @DaysToRetain

--delete restorehistory
delete restorehistory 
where restore_date < GetDate()- @DaysToRetain

--delete backupfile
delete backupfile from backupset bs
join backupfile bf on bf.backup_set_id = bs.backup_set_id
where bs.backup_finish_date < GetDate()- @DaysToRetain

--delete backupset
delete backupset
where backup_finish_date < GetDate()- @DaysToRetain

--delete backupmediafamily
delete backupmediafamily from backupmediaset bms 
left join backupset bs on bms.media_set_id = bs.media_set_id
join backupmediafamily bmf on bmf.media_set_id = bms.media_set_id
where bs.backup_set_id is null

--delete backupmediaset
delete backupmediaset from backupmediaset bms 
left join backupset bs on bms.media_set_id = bs.media_set_id
where bs.backup_set_id is null

SET NOCOUNT OFF