purging msdb history - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

purging msdb history

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-01-20 : 08:37:17

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

22859 Posts

Posted - 2006-01-20 : 10:56:23

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

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-01-20 : 11:35:36

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

Kristen
Test

22859 Posts

Posted - 2006-01-20 : 11:54:27

"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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources