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 2 years, 10 months ago by  rr_littletons.
    • This topic was modified 2 years, 10 months 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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

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

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