Weekly Dynamic: SQL Script to Monitor Transaction Log Backups

We see a surprising number of huge transaction logs in clients who should know better. This commonly happens when transaction logs are not being backed up regularly and the database is running in Full recovery mode. If the log grows large enough, SQL Server can stop working leading to a lot of excitement. Typically, this solved with a properly setup maintenance plan in SQL Server but what if that's not the case? What if the maintenance plan is failing and not providing notifications? Short of regularly monitoring transaction log growth, how can a DBA ensure that logs are being properly truncated?

Thanks to the MSSQLTips site, we've now got this SQL Script designed to show any databases in Full or Bulk-Logged recovery model that have had a full backup without any subsequent transaction log backups.

SELECT
D.[name] AS [database_name], D.[recovery_model_desc] 
FROM
sys.databases D LEFT JOIN 
(
   SELECT BS.[database_name], 
   MAX(BS.[backup_finish_date]) AS [last_log_backup_date] 
   FROM msdb.dbo.backupset BS 
   WHERE BS.type = 'L' 
   GROUP BY BS.[database_name] 
   ) BS1 
ON D.[name] = BS1.[database_name]
LEFT JOIN 
(
   SELECT BS.[database_name], 
   MAX(BS.[backup_finish_date]) AS [last_data_backup_date] 
   FROM msdb.dbo.backupset BS 
   WHERE BS.type = 'D' 
   GROUP BY BS.[database_name] 
) BS2 
ON D.[name] = BS2.[database_name]
WHERE
D.[recovery_model_desc] <> 'SIMPLE'
AND BS1.[last_log_backup_date] IS NULL OR BS1.[last_log_backup_date] < BS2.[last_data_backup_date]
ORDER BY D.[name];

This script returns database names and recovery models allowing administrators to dig deeper into the underlying problem. Running this script is much easier than monitoring transaction log growth and it can save a lot of pain and expense by helping keep the size of log files manageable.

More information on this script and similar scripts is available from MSSQLTips.com