We help IT Professionals succeed at work.

Help on SQL Server Database availability report

tschary
tschary asked
on
748 Views
Last Modified: 2016-02-09
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
Comment
Watch Question

David ToddSenior Database Administrator
CERTIFIED EXPERT

Commented:
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 ToddSenior Database Administrator
CERTIFIED EXPERT

Commented:
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.

Author

Commented:
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.
David ToddSenior Database Administrator
CERTIFIED EXPERT

Commented:
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

Author

Commented:
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 ToddSenior Database Administrator
CERTIFIED EXPERT

Commented:
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
Senior Database Administrator
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Hi David,

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

Regards,
Sreenivasa

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.