January 25, 2022 at 1:56 am
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()))
January 25, 2022 at 2:46 pm
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 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy