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