Post reply

Last Successful TLog Backup (Hr) Help

  • January 25, 2022 at 1:56 am

    #3976780

    I'm needing assistance completing the following code.  This query needs to run the number of hours since last successful tlog backup. There are 3 cases when I get a return, No Db on the instance, No Tlog has ever run and Tlog is less than 1hr old.  For any and all cases, I need the results to return the number 0.  Any ideas are appreciated.

    declare @dbcount int = (select count(*) from master.sys.databases where name not in ('tempdb', 'master', 'model', 'msdb'))

    if (@dbcount = 0)

    select 0 as [backup age (hours)]

    else

    select top 1

    case when max(bs.backup_finish_date) is null then datediff(hh, max(bs.backup_finish_date), getdate())

    else

    0

    end

    as [backup age (hours)]

    from

    msdb.dbo.backupset bs

    join master.sys.databases d

    on d.name = bs.database_name

    where

    bs.type = 'L' and

    database_name not in ('tempdb', 'master', 'model', 'msdb') and d.state = 0 and d.source_database_id is null

    group by

    bs.database_name

    having

    (max(bs.backup_finish_date) < dateadd(MINUTE, - 15, getdate()))

    • This topic was modified 3 months, 3 weeks ago by  rr_littletons.
    • This topic was modified 3 months, 3 weeks ago by  rr_littletons. Reason: Update Title and move to new form

  • January 25, 2022 at 2:46 pm

    #3976998

    I'm not exactly sure what result you want, but if you want only a single result you need to add an ORDER BY to the query.  A TOP (1) without an ORDER BY can randomly return any row from the result set.

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply