The following T-SQL writes errors into SQL log when there are databases have not been backed up in last 12 hours:
IF EXISTS (
SELECT bs.database_name, MAX(bs.backup_finish_date) AS LatestDatabaseBackupDate
FROM msdb.dbo.backupmediafamily bmf
INNER JOIN msdb.dbo.backupset bs ON bmf.media_set_id = bs.media_set_id
INNER JOIN master.sys.databases d ON d.name = bs.database_name
WHERE bs.type = 'D' and d.name <> 'tempdb' and d.state = 0 and d.source_database_id is null
GROUP BY bs.database_name HAVING DATEDIFF(HH, MAX(bs.backup_finish_date), GETDATE()) > 12
) RAISERROR(N'On TESTSERVER\TESTINST one or more databases have not been backed up in last 12 hours, please check..', 17, 1) with log
Check the log as below:
ALso we can check spid 55 details:
select * from sys.sysprocesses where spid =55;
Make DBA Life Easy 