Avatar of tschary
tschary asked on

Help on SQL Server Database availability report

Hi Experts,
I want a database availability monthly report. I need report of the each SQL database Uptime(online) and downtime(offline) in a  month. Example: dbname  uptime 714:30:52, downtime 15:30:08(HH:MM::SS).

Please help me how to develop it. Any script is available, then please share me.

Thanks,
Sreenivasa
Microsoft SQL Server 2008

Avatar of undefined
Last Comment
tschary

8/22/2022 - Mon
David Todd

Hi,

Do you mean Database or Database Server uptime?

The information is there in the System Event Log and not sure where else.

Regards
  David
David Todd

It occurs to me that perhaps the best way to get this is via a custom monitoring solution. Figure out what your resolution is - perhaps 5mins and then create a job to log which databases are up every five minutes.
ASKER
tschary

Hi David, Thanks for reply. I need a database uptime and down time report on monthly basis. I need it for all SQL Server instances in my project environment. we have a KPI for this. Yes,We need a custom monitoring solution.Please help me how to achieve this.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
David Todd

Hi,

Here is a query extracted from my powershell script that I run against all my servers once a day.

select 
	getdate() as LogDateTime
    , @@servername as MachineName
	, @@servername as ServerName
	, substring( @@version, 30, 8 ) as ProductVersion
	, 'RTM' as ProductLevel
	, left( substring( replace( replace( replace( @@version, char( 9 ), '' ), char( 10 ), '' ), char( 13 ), '' ), 117, 16 ), 50 ) as Edition
	, sd.name as DatabaseName
    , sum( fi.size ) * 8 / 1024.0 / 1024.0 as DataSizeGB
	, sum( ti.size ) * 8 / 1024.0 / 1024.0 as LogSizeGB
from master.dbo.sysdatabases sd
left outer join (
	select
		fii.dbid
		, sum( fii.size ) as size
	from master.dbo.sysaltfiles fii
	where
		fii.groupid > 0 -- not logs
	group by
		fii.dbid
	) fi
	on fi.dbid = sd.dbid
left outer join (
	select	
		tii.dbid
		, sum( tii.size ) as size
	from master.dbo.sysaltfiles tii	
	where
		tii.groupid = 0 -- logs
	group by
		tii.dbid
	) ti
	on ti.dbid = sd.dbid
group by
    sd.name

Open in new window


Now if that we running more frequently than once a day, like every five minutes, you could count how many times the server\instance.database was logged in an hour and do some rough maths.  Expected is 12 logs per hour so that equates to 60mins uptime. If only logged 11 times then that equates to 55mins uptime. Given that you are running this quite frequently then I don't see the need to re-run if one run has issues - that's sort of the point to the whole exercise. If the monitoring can't connect because the server is too busy and the connection times out, I suggest, for the purpose of your SLA, that this server and its databases weren't available.

Does that help?

Regards
  David
ASKER
tschary

Hi David,The script is logging database information.I want each database uptime and downtime for all sql servers(150) in my environment.Please provide me a better custom monitoring solution. Thanks for support.

Regards,
Sreenivasa
David Todd

Hi Sreenivasa,

You can't use Powershell to iterate over a group of servers and run the above query against each one?

Probably will need to use a proxy account in SQL Agent, which is based on a credential which is based on an AD account.

Regards
  David
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
David Todd

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
tschary

Hi David,

I will try your solution and get back to you. thanks for your support.

Regards,
Sreenivasa