Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
AskSQLTeam
Ask SQLTeam Question
0 Posts |
Posted - 2006-12-22 : 07:47:52
|
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
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2006-12-22 : 09:13:17
|
look up an item on this posted in the past by member "tkizer". it may be on her blog. |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-12-22 : 10:20:54
|
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 ondeclare @purge_date datetimedeclare @cutoff_date datetime-- Set purge cutoff to 30 days backset @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 |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-12-22 : 11:07:34
|
"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+MassiveKristen |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-12-22 : 11:56:38
|
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+MassiveKristen
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 |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-12-23 : 02:54:25
|
"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 |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-12-23 : 17:57:15
|
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 |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-12-24 : 06:23:22
|
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 |
|
|
|
|
|
|
|