SQL Server 2000 backuphistory quirk

We came across this recently when we tried to delete a database and found that the server was churning away for hours at > 50% on each CPU. As Andrew's article explains this is because the backuphistory table in msdb is never cleared out - ours had > 250K rows and hence the load when sp_delete_backuphistory is called when a database is deleted!

If you have lots of databases that are backed up and hence a large table - like ours - you really want to step through a month at a time.

declare @xtime datetime
set @xtime = cast('04/01/2003' as datetime)
use msdb
exec sp_delete_backuphistory @xtime;

Once you have it cleared out then you probably want to add a nightly job to clean it up. Add something like the following procedure to to msdb:

create procedure TidyBackupHistoryTo60Days
as
-- delete backup entries older than 60 days
-- run as nightly job to keep msdb tidy
-- procedure must be stored in msdb as it uses
-- sp_delete_backuphistory which is defined there
begin
declare @xtime as datetime
set @xtime = dateadd(d,-60,getdate())
exec sp_delete_backuphistory @xtime
end