When was the last transaction log backup?
I was performing a health check on a customer's inventory recently, and I produced a list of databases where the transaction log was larger than the data file. Of course you know, this can happen if the transaction log dumps aren't occurring, or aren't occurring often enough. The Customer was fairly adamant that the logs were being backed up, though, so I needed a quick way to confirm the last backup time, per database.
Run this. It will give you the RECOVERY MODEL and last transaction log dump time for all of your databases.
Run this. It will give you the RECOVERY MODEL and last transaction log dump time for all of your databases.
SELECT
d.name [Database],
d.recovery_model_desc [RecoveryModel],
MAX(b.backup_finish_date) [LastTranLogBkup]
FROM
master.sys.databases d LEFT OUTER JOIN msdb..backupset b
ON b.database_name = d.name
AND b.type = 'L'
GROUP BY
d.name,
d.recovery_model_desc
ORDER BY
LastTranLogBkup DESC