set nocount on
declare @purge_date datetime
declare @cutoff_date datetime

-- Set purge cutoff to 30 days back
set @cutoff_date = dateadd(dd,datediff(dd,0,getdate())-30,0)

print	'Purge backup history before Cutoff Date = ' +
	convert(varchar(10),@cutoff_date ,121)

while	1 = 1
	begin

	set @purge_date = null
	
	-- Find date of oldest backup set
	select
		@purge_date = dateadd(dd,datediff(dd,0,min(backup_finish_date))+1,0)
	from
		msdb.dbo.backupset
	where
		backup_finish_date <= @cutoff_date

	if @purge_date is null or @purge_date > @cutoff_date
		begin
		print 'Purge backup history complete through '+
			convert(varchar(10),@cutoff_date ,121)
		break
		end
	
	print	char(10)+char(13)+'Purging backup history before ' +
		convert(varchar(10),@purge_date,121) +char(10)+char(13)
	
	select
		[Backup Sets to be Deleted Count ] = count(*) 
	from
		msdb.dbo.backupset
	where
		backup_finish_date < @purge_date
	
	exec msdb.dbo.sp_delete_backuphistory @purge_date

	end  -- End While

quote:

---

Originally posted by Kristen

"You should be OK using sp_delete_backuphistory if you limit it to purging one day at a time and run it a as scheduled job every day to keep the history under control."

Only, IME< if you have always done this and your MSDB has not got "out of control". But even then with, say, 100 DBs on a server and TLog backups every 10 minutes I expect it would get slow very quickly - e.g. if you kept a month of backup history.

See: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=MSDB+Massive

Kristen

---