SQL Server: Monitor jobs

                             DataBases   Error log check  and master is backed up every day at 1 am, so this should report success  though  job  generate 

declare @sql varchar(50) 

declare @mins_before int

select @mins_before = 15

if object_id('tempdb..#errorlog') is not null

begin

  drop table #errorlog

end

create table #errorlog

(

  EntryTime datetime,

  Source varchar(50),

  LogEntry varchar(4000)

)

select @sql = 'exec master..sp_readerrorlog'

insert into #errorlog

execute (@sql)

if exists

(

 select *

 from #errorlog

 where DATEDIFF(mi, EntryTime, GETDATE()) < @mins_before

 and Source = 'backup'

 and LogEntry like 'Database backed up. Database: master%'

)

and not exists

(

 select *

 from #errorlog

 where DATEDIFF(mi, EntryTime, GETDATE()) < @mins_before

 and Source = 'backup'

 and LogEntry like 'Error%' 

)

begin

  select 'ok, we have a successful backup'

  select *

  from #errorlog

  where DATEDIFF(mi, EntryTime, GETDATE()) < @mins_before

  and Source = 'backup'

  and LogEntry like 'Database backed up. Database: master%'

  order by EntryTime

end

else

begin

  select 'not ok, we do not have a successful backup'

  select *

  from #errorlog

  where DATEDIFF(mi, EntryTime, GETDATE()) < @mins_before

  order by EntryTime

declare @servername varchar(50)

declare @recipients varchar(200)

declare @subject varchar(100)

Select @servername = @@servername,

@recipients =  'XYZ@a.com',

@subject = @servername + ' – backup database failed'

exec msdb..sp_send_dbmail

@profile_name = @servername,

@recipients = @recipients,

@subject = @subject,

@body = @subject

end

if object_id('tempdb..#errorlog') is not null

begin

  drop table #errorlog

end