Friday, May 30, 2014

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 [Database],
       d.recovery_model_desc [RecoveryModel],
       MAX(b.backup_finish_date) [LastTranLogBkup]
  master.sys.databases d LEFT OUTER JOIN msdb..backupset b
         ON b.database_name =
         AND b.type = 'L'
       LastTranLogBkup DESC

No comments:

Post a Comment