Article ID: 152354
Article Last Modified on 9/27/2004
use master
go
sp_configure 'allow', 1
go
reconfigure with override
go
drop proc sp_cleanbackupRestore_log
go
create proc sp_cleanbackupRestore_log
@DeleteBeforeDate datetime
as
begin
Delete from msdb.dbo.sysbackupdetail where backup_id
in (Select backup_id from msdb.dbo.sysbackuphistory where backup_start <=
@DeleteBeforeDate)
Delete from msdb.dbo.sysbackuphistory where backup_start <=
@DeleteBeforeDate
Delete from msdb.dbo.sysrestoredetail where restore_id
in (Select restore_id from msdb.dbo.sysrestorehistory where backup_start <=
@DeleteBeforeDate)
Delete from msdb.dbo.sysrestorehistory where backup_start <=
@DeleteBeforeDate
end
go
sp_configure 'allow', 0
go
reconfigure with override
You will then need to run the newly created stored procedure. For example, if you wanted to delete all the entries in the tables listed in the stored procedure that occured before January 2, 1997, you would run the following:
exec sp_cleanbackupRestore_log '1/2/97'If you wish to automate the code, you can use something similar to the following:
declare @DeleteBeforeDate datetime -- Modify the second parameter as necessary. -- It is currently set to delete anything older than 60 days. select @DeleteBeforeDate = DATEADD(day, -60, getdate()) select @DeleteBeforeDate exec sp_cleanbackupRestore_log @DeleteBeforeDateNOTE: If you receive an 1105 for object 'syslogs' please see the following article in the Microsoft Knowledge Base: 110139 - INF: Causes of SQL Transaction Log Filling Up.
Keywords: kbprb KB152354