SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Alternative to sp_delete_backuphistory
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 12/22/2006 :  07:47:52  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
Edwardb writes "Hi,
We have an SQL 2000 server with MSDB database 8gb in size.
The database is running a number of databases that are being Log Shipped.
Restoring a database is almost unusable from enterprise manager because of the size of MSDB.
I have tried running sp_delete_backuphistory to clean up msdb but the process fails with a 'locking message' and at the same time a lot of the Log Shipping jobs fail.
Can anybody help me with an alternative method to cleaning up MSDB.
thanks."

AndrewMurphy
Flowing Fount of Yak Knowledge

Ireland
2916 Posts

Posted - 12/22/2006 :  09:13:17  Show Profile  Reply with Quote
look up an item on this posted in the past by member "tkizer". it may be on her blog.
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 12/22/2006 :  10:20:54  Show Profile  Reply with Quote
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.

Here is a script that will purge the backup history down to 30 days. You can adjust the number of days back to purge to suit your needs. If you have that much history, it will take a lot time to complete the first time, but after that the daily purge will be fast.


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





CODO ERGO SUM
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 12/22/2006 :  11:07:34  Show Profile  Reply with Quote
"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
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 12/22/2006 :  11:56:38  Show Profile  Reply with Quote
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



What would get slow? The purge, or attempting to use the backup history for a restore?

When we first started running this, they were out of control, but it was just a matter of letting it run until it removed all the old history, and after that letting it run daily. We never had a problem with either the initial purge or the daily maintenance purge. However, none of our msdb databases had reached the 8 GB size.

I can't say I have ever seen a server with hundreds of databases where all are doing transaction log backups, so I don't know what that would do.






CODO ERGO SUM
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 12/23/2006 :  02:54:25  Show Profile  Reply with Quote
"What would get slow"

sp_delete_backuphistory - the way it is written is appallingly slow [in SQL2000 at least] for any reasonable number of records, and 24 hours * 6 backups per hour [every 10 minutes] * 30 days in month * 100 databases is getting on for 0.5 million rows.

"I can't say I have ever seen a server with hundreds of databases where all are doing transaction log backups"

Its not so much the number of DBs as the number of rows in MSDB history table.

So 100 databases and TLog backup every minute, plus keep the history for 30 days would generate enough records to be unmanageable. In our case we had about 30 databases but hadn't cleared MSDB for a couple of years ... which was also unmanageable!

Our MSDB was only 750MB in size but I calculated that sp_delete_backuphistory was going to take months of continuous running to clear the backlog - goodness knows how long it would take for 8GB MSDB :-(

Kristen
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 12/23/2006 :  17:57:15  Show Profile  Reply with Quote
I believe the largest msdb that we had was about 450 MB, and we purged it off in about 1 day. I don't remember the exact elapsed time, because that was about 3 years ago.

I agree that sp_delete_backuphistory is a little slow, but that is why we run the script below as a scheduled job once a day. It takes a few seconds at most, so it doesn't seem worth the effort on our systems to go with something else.




CODO ERGO SUM
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 12/24/2006 :  06:23:22  Show Profile  Reply with Quote
No, I agree with that. The pain is when you come across a server where it hasn't been scheduled and there is a huge backlog to clear ...

Kristen
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000