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)
 purging msdb history
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 01/20/2006 :  08:37:17  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
Bill writes "Sqlserver Standard Edition 8.00.760 (SP5) running on Windows NT 5.0 (2195).
I'm looking for a way to periodically purge all msdb history tables. I've found parts of this, but no comprehensive method that includes all history tables. I am wondering if the following steps will prevent the msdb database from excesive growth:
1) In the maintenance plan wizard reporting tab, always set the numer of rows to retain to some reasonable number; this will prevent sys_dbmaintplanhistory from growing too large.
2) Run sp_delete_backuphistory (or one of your scripts, isp_DeleteBackupHistory) to delete old backup/restore records.
3) Run sp_purge_jobhistory to delete history records for other non-maintenanceplan jobs.
Am I missing anything? Are there other history tables that these steps will not affect?
Thanks."

Kristen
Test

United Kingdom
22403 Posts

Posted - 01/20/2006 :  10:56:23  Show Profile  Reply with Quote
Hi Bill, Welcome to SQL Team!

Not quite the answer to your question, but you might want to have a look at:

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

Edit: Direct link bypassing FGA: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=36201)

Kristen

Edited by - Kristen on 01/20/2006 11:59:05
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 01/20/2006 :  11:35:36  Show Profile  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 01/20/2006 :  11:54:27  Show Profile  Reply with Quote
"It may run a long time the first day"

Watch out for that ... in the thread I posted a link to I explained my dilemma sometime ago when we looked into this. It would have taken 5 days continuous running to clear the back history. We reduced the runtime to clear a 10 day log from 2hours 5 minutes to just 32 seconds (using an Sproc that Tara posted in that thread, I'm pretty sure its in her Blog too).

It also transpired that there are some slightly awkward referential integrity issues too - which means that some simple deletes can fail - but they are self correcting as the following run will have deleted the rows int he corresponding tables, so it should then clear the ones that got left behind the previous day too.

But the bottom line is that code in sp_delete_backuphistory is horribly inefficient, and might take a really REALLY long time to run the first time.

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.05 seconds. Powered By: Snitz Forums 2000