유지관리 - 흔적삭제

대략... Job로그 등의 삭제와

필요없는 과거 DB Backup File의 삭제

declare @DeleteDate nvarchar(50)

declare @DeleteDateTime datetime

 

set @DeleteDateTime = DateAdd(dd, -10, GetDate())

set @DeleteDate = (Select Replace(Convert(nvarchar, @DeleteDateTime, 111), '/', '-') + 'T' + Convert(nvarchar, @DeleteDateTime, 108))

 

EXECUTE master.dbo.xp_delete_file 0,N'E:\DeleteTest',N'bak',@DeleteDate,1

 

 

/*

       CleanUp Task

*/

declare @dt datetime

 

set @dt = dateadd(m,-7,getdate())

 

EXEC msdb.dbo.sp_delete_backuphistory @oldest_date=@dt

EXEC msdb.dbo.sp_purge_jobhistory @oldest_date=@dt

EXEC msdb.dbo.sp_maintplan_delete_log @oldest_time=@dt

EXEC msdb.dbo.sysmail_delete_mailitems_sp @sent_before=@dt