I've had this problem , where I would forget checking when transaction log backup ran last. I would check for failure of the job, but some one would have disabled the Xaction log backup for some reason and would have never turned it back. I finally wrote a script to send me alert if the Xaction Log backup had not run for more than 15min (Thats how often it is supposed to run). Here is the script hope this is useful

Declare @i_CurDate int,

 @i_CurTime int,

 @i_MaxDt int,

 @i_MaxTime int

--Get Last Run Date time of Xaction Log Backup

select @i_MaxDt = max(run_Date),

 @i_MaxTime = max(run_time)

 from sysjobhistory where job_id in (

select job_id from sysjobs where name ='BACKUPNAME')

and run_status=1

--Convert Current Date and Time to Int format

select @i_CurDate = Cast(convert(Char(20),getdate() ,112) as int)

select @i_CurTime =(Cast(left(convert(varchar(10),getdate() ,108),2) as int)* 100 +

 Cast(substring(convert(varchar(10),getdate() ,108),4,2) as int)

&nbsp *100 + Cast(right(convert(varchar(10),getdate() ,108),2) as int)

--If Difference between Last Run and Current Time > 1500(15 minutes), Then Notify

If (@i_CurDate-@i_MaxDt) + (@i_CurTime-@i_MaxTime) > 1500

Begin

 Exec master..xp_sendmail @recipients = 'recipients' ,@message = 'Check XXX Transaction Log backup', @subject = 'Check XXX Transaction Log backup',@no_output = 'TRUE'

End

Thanks

Sreejith