The housekeeping task of MSDB
Posted on 2012-04-10
I found out that if I use the SSIS method to create the history cleanup task, which is only designed for MSDB, I can generate the following t-SQL script:
declare @dt datetime select @dt = cast(N'2012-03-13T19:21:30' as datetime) exec msdb.dbo.sp_delete_backuphistory @dt
EXEC msdb.dbo.sp_purge_jobhistory @oldest_date='2012-03-13T19:21:30'
EXECUTE msdb..sp_maintplan_delete_log null,null,'2012-03-13T19:21:30'
But it seems that it "Hard code" a date for me and I can't see how can I make the script dynamic so that the current date is the current data everyday. So I can script this one to safely remove historical data older than 2 weeks.