Last Successful TLog Backup (Hr) Help

• rr_littletons

  Old Hand

  Points: 343

  More actions

  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 years, 6 months ago by rr_littletons.
  • This topic was modified 3 years, 6 months ago by rr_littletons. Reason: Update Title and move to new form

• ScottPletcher

  SSC Guru

  Points: 101192

  More actions

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