Help on SQL Server Database availability report

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

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.

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.

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

Select allOpen 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

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

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

Hi David,

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

Regards,
Sreenivasa