I just run this script every day in a scheduled job to keep the size of msdb under control. It may run a long time the first day, but after that it should take only a few seconds per day. You can change the number of days of data you want to maintain.
set nocount on
declare @purge_date datetime
declare @days_back int
set @days_back = 30
while exists
(
select
top 1 backup_finish_date
from
msdb.dbo.backupset
where
datediff(dd,backup_finish_date,getdate()) > @days_back
)
begin
select @purge_date =
convert(char(10),dateadd(dd,1,min(backup_finish_date)),101)
from
msdb.dbo.backupset
where
datediff(dd,backup_finish_date,getdate()) > @days_back
print 'Purging backup history before ' + convert(char,@purge_date) +char(10) +char(13)
select
count(*) Backup_Set_Count
from
msdb.dbo.backupset
where
backup_finish_date < @purge_date
exec msdb.dbo.sp_delete_backuphistory @purge_date
end
CODO ERGO SUM
purging msdb history
Posted - 01/20/2006 : 08:37:17
