Kristen recently asked why his msdb database had grown so large (750MB).  He found out that it was due to the sizes of the backup system tables.  These tables store information used for backup and restore operations.  If you've ever used the restore database wizard in Enterprise Manager (and if you have, you might want to start using Query Analyzer instead to get more familiar with backup and restore operations), you might notice that it displays a listing of previous backups to choose from for the restore.  It gathers this information from these backup system tables.  If you backup your transaction logs as often as we do (every 15 minutes), these tables can get quite large.  Microsoft provides sp_delete_backuphistory to delete this data.  As Kristen found out, this stored procedure is very slow as it uses a cursor to loop through the data.  Using sp_delete_backuphistory took over 2 hours to delete all but 10 days worth of data.  I provided to him my version of this stored procedure that uses JOINs instead of a cursor.  With slight modifications, he was able to get it working for him.  This version was able to delete all but 10 days worth of data in 32 seconds.  Here's the stored procedure:

CREATE PROC isp_DeleteBackupHistory
(@DaysToRetain int)
AS

SET NOCOUNT ON

DECLARE @Err int
DECLARE @rc int

BEGIN TRAN

 DELETE FROM msdb..restorefile
 FROM msdb..restorefile rf
 INNER JOIN msdb..restorehistory rh
 ON rf.restore_history_id = rh.restore_history_id
 INNER JOIN msdb..backupset bs
 ON rh.backup_set_id = bs.backup_set_id
 WHERE bs.backup_finish_date < (GETDATE() - @DaysToRetain)

 SET @Err = @@ERROR

 IF @Err <> 0
  GOTO Error_Exit
 
 DELETE FROM msdb..restorefilegroup
 FROM msdb..restorefilegroup rfg
 INNER JOIN msdb..restorehistory rh
 ON rfg.restore_history_id = rh.restore_history_id
 INNER JOIN msdb..backupset bs
 ON rh.backup_set_id = bs.backup_set_id
 WHERE bs.backup_finish_date < (GETDATE() - @DaysToRetain)

 SET @Err = @@ERROR

 IF @Err <> 0
  GOTO Error_Exit
 
 DELETE FROM msdb..restorehistory
 FROM msdb..restorehistory rh
 INNER JOIN msdb..backupset bs
 ON rh.backup_set_id = bs.backup_set_id
 WHERE bs.backup_finish_date < (GETDATE() - @DaysToRetain)

 SET @Err = @@ERROR

 IF @Err <> 0
  GOTO Error_Exit
 
 SELECT media_set_id, backup_finish_date
 INTO #Temp
 FROM msdb..backupset
 WHERE backup_finish_date < (GETDATE() - @DaysToRetain)

 SET @Err = @@ERROR

 IF @Err <> 0
  GOTO Error_Exit
 
 DELETE FROM msdb..backupfile
 FROM msdb..backupfile bf
 INNER JOIN msdb..backupset bs
 ON bf.backup_set_id = bs.backup_set_id
 INNER JOIN #Temp t
 ON bs.media_set_id = t.media_set_id
 WHERE bs.backup_finish_date < (GETDATE() - @DaysToRetain)

 SET @Err = @@ERROR

 IF @Err <> 0
  GOTO Error_Exit
 
 DELETE FROM msdb..backupset
 FROM msdb..backupset bs
 INNER JOIN #Temp t
 ON bs.media_set_id = t.media_set_id

 SET @Err = @@ERROR

 IF @Err <> 0
  GOTO Error_Exit
 
 DELETE FROM msdb..backupmediafamily
 FROM msdb..backupmediafamily bmf
 INNER JOIN msdb..backupmediaset bms
 ON bmf.media_set_id = bms.media_set_id
 INNER JOIN #Temp t
 ON bms.media_set_id = t.media_set_id

 SET @Err = @@ERROR

 IF @Err <> 0
  GOTO Error_Exit
 
 DELETE FROM msdb..backupmediaset
 FROM msdb..backupmediaset bms
 INNER JOIN #Temp t
 ON bms.media_set_id = t.media_set_id

 SET @Err = @@ERROR

 IF @Err <> 0
  GOTO Error_Exit

COMMIT TRAN

SET @rc = 0

GOTO isp_DeleteBackupHistory_Exit

Error_Exit:

ROLLBACK TRAN

SET @rc = -1

isp_DeleteBackupHistory_Exit:

DROP TABLE #Temp

SET NOCOUNT OFF

RETURN @rc

GO