Thursday 24 March 2011

Generate Alert if job has not run for more than 2 days in 2005

declare @LSTexecDateInt varchar(8), @LSTexecDate datetime
declare @days int , @test varchar(250)
set @LSTexecDateInt = (select max(run_date)
from sysjobhistory where job_id = 'F7FF3E3A-97FC-42F6-84FB-7746138F5432' )
--Set @LSTexecDateInt = '20110225'
Set @LSTexecDate = Convert(datetime,left(@LSTexecDateInt,4) + '-' + left(right(@LSTexecDateInt,4),2) + '-' + right(@LSTexecDateInt,2))
Set @days = datediff(dd,@LSTexecDate ,getdate())
--print @days

if (@days)>2
begin
set @test = 'Backup for Master and MSDB database in server A  not taken from '+ convert(varchar(3) , @days) +'    days'
EXEC msdb.dbo.sp_send_dbmail  @recipients =
                                          @body = @test,
@subject = 'Backup for Master server  master database  not taken ',
@profile_name = 'DB profile name'
end

No comments:

Post a Comment