How to query idle/down state of SQL Server? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to query idle/down state of SQL Server?

Author

Topic

barnabeck
Posting Yak Master

236 Posts

Posted - 2013-04-08 : 04:01:34

We are capturing temperature and humidity in one of our laboratories and there is a job that checks every 15 minutes if these values are within a given range or if the measurement system that feeds the database is idle and had captured data within the last 15 minutes. Otherwise an email is sent and a technician has to show up and fix the problem.

This weekend we had a problem with the SQL Server which caused a reboot and as a consequence the laboratory alert was fired as soon as the Server was up again, as there hadn't been coming in any measurements during downtime – logically! The technician showed up and everything looked ok to him.

Therefore I would like to include in that query that checks the existence of any measurement in the last 15 minutes the state of the SQL Server by that time. Only if the system had been idle at that point, the conclusion that there has been a technical problem with the measurement is legitimate and an alert should be sent.

Anybody has an idea how to achieve this?

IF EXISTS
(select a.delta from (select TOP 1 DATEDIFF(MINUTE, SQL_DATETIME,getdate()) as delta
                     from dbo.Ambient
                     order by ID DESC) a
where a.delta >30)

BEGIN

….

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-08 : 09:26:14

Looking up when the tempdb was created will tell you how long the server has been up.

select create_date, DATEDIFF(mi,create_date,GETDATE()) as UptimeMinutes
 from sys.databases where name ='tempdb'

But will that solve your problem? What if the server went down and did not come back up for 3 hours?

barnabeck
Posting Yak Master

236 Posts

Posted - 2013-04-08 : 10:16:09

As I found out now, you can get the time the server started with:

SELECT sqlserver_start_time
FROM sys.dm_os_sys_info

which happens 13 seconds before the tempdb is created - in my case.

What I decided to do is to check when the job had been executed successfully for the last time; you have to trace the jobs id first.
If this time difference is smaller than the scheduled repetition of the job, any unavailability of data must be related to a failure of the measuring. If the time gap is bigger, then the Server must have had downtime in the meantime.

select DATEDIFF(MINUTE,last_executed_step_date, GETDATE()) from msdb.dbo.sysjobactivity
where job_id ='0A362493-B516-4EC2-8189-983584A96CF5' and DATEDIFF(MINUTE,GETDATE(), next_scheduled_run_date) >0

If the server doesn't reboot successfully, ... now this is a good question?! Can I check from other SQL servers which istances are currently running?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources