SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to query idle/down state of SQL Server?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

barnabeck
Posting Yak Master

Spain
187 Posts

Posted - 04/08/2013 :  04:01:34  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3332 Posts

Posted - 04/08/2013 :  09:26:14  Show Profile  Reply with Quote
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?
Go to Top of Page

barnabeck
Posting Yak Master

Spain
187 Posts

Posted - 04/08/2013 :  10:16:09  Show Profile  Reply with Quote
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?
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000