Weekly Review: 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

Originally Posted by Mark at 1/11/2010 09:00:00 AM