We had some issues today with our backup system Commvault and because of this we had no SQL Logbackups for the past 24 hours 😐 I noticed this doing the morning check. This was a bit late if you ask me so I decide to make a check.  I made this SQL script

declare @ErrorMessage varchar(500)
declare @dbname varchar(128)
declare @var varchar(max)

SELECT @var = COALESCE(@var + ', ' + s.DatabaseName, s.DatabaseName)
FROM 
(
  SELECT 
         b.database_name as 'DatabaseName'
        ,b.type as 'BackupType'
    ,DATEDIFF(MINUTE,B.BACKUP_FINISH_DATE,GETDATE()) AS 'LastLogBackupPastMinutes'
    ,ROW_NUMBER() OVER (PARTITION BY b.database_name,b.type ORDER BY backup_finish_date DESC) AS 'Backuprank'
  FROM msdb..backupset as b
  WHERE B.DATABASE_NAME IN (SELECT NAME FROM sys.databases WHERE recovery_model_desc <> 'Simple' and state_desc = 'ONLINE' and user_access_desc = 'MULTI_USER')
) AS S
WHERE backuprank = 1 and BackupType = 'L' and LastLogBackupPastMinutes > 60
IF (SELECT @var) is not null
BEGIN
  SET @ErrorMessage = 'For the past 60 minutes there were no SQL logbackups made. The effected database(s) are :'+@var+'. Checks whats going on.'
  RAISERROR(@ErrorMessage, 16,1)
END 
GO

this script checks if there where SQL Logbackups made for the past 60 minutes. If not it raises a custom error. I put this script in an SQL Agent Job en run it every 60 minute. When there are no logbackups the agent fails and send me a job fail notification.