Search This Blog

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.
   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