Database Management

SQL Server missing transaction logs

Is there there an easy way to determine if you are missing transaction logs for a SQL Server database?

Yes!

Try the following code:

SELECT D.[name] AS [db_name], D.[rec_model_desc]
FROM sys.databases D LEFT JOIN
(
SELECT BS.[db_name],
MAX(BS.[backup_finish_date]) AS [last_log_backup_date]
FROM msdb.dbo.backupset BS
WHERE BS.type = ‘L’
GROUP BY BS.[db_name]
) BS1 ON D.[name] = BS1.[db_name]
WHERE D.[rec_model_desc] <> ‘SIMPLE’
AND BS1.[last_log_backup_date] IS NULL
ORDER BY D.[name];