Find databases that do not have transaction log backups

How do you detect that there are databases in the full recovery model that do not have scheduled transaction log backups? Many people have no idea and only notice when the disk on which the transaction log is stored becomes full and the databases can no longer be used.

Here is a script to see if you have any databases that have not had any transaction log backups and how long ago they were last run if any.

SELECT D.Name as databasNamn,
    ISNULL(CAST(DATEDIFF(hh, MAX(BS.backup_finish_date), Getdate()) AS nchar(125)), 'TLog Backupp saknas') as timmarSedanSenastTLogBackupp
FROM sys.databases d
LEFT OUTER JOIN msdb.dbo.backupset bs
ON d.name = bs.database_name AND bs.type = 'L' -- Log backupp
WHERE d.recovery_model_desc = 'FULL' -- bara databaser med full recovery model behöver tlog backupper
AND d.database_id <> 2 -- exkludera tempdb
GROUP BY d.Name
ORDER BY d.Name

/Björn