Link to home
Start Free TrialLog in
Avatar of dbaSQL
dbaSQLFlag for United States of America

asked on

Near realtime alert if SQL Server services stop.

I was checking one of my customer's servers the the other day, and I found that the sql server services were not running on the mirror partner in a team of mirrored servers.  Once I brought it up, I found that the services were stopped on that instance for 16 days.  The server was basically down for more than two weeks, yet nobody knew about it.    Not good.

I need the fastest and most effective means of sending an alert notification if either the SQL Server or SQL Server Agent services stop running.  I don't want to know later if they were not running at such and such time.  I want to be notified when they stop running.  We are building a CMS that will ultimately drive this from, but for now I need to get something setup asap, and I am only monitoring three servers at this location.  I've looked at xp_servicecontrol, but I don't know how to use that remotely.  

Any ideas?
Avatar of Jeff Edmunds
Jeff Edmunds
Flag of United States of America image

Check out the Powershell script from the Scripting Guy on Microsoft's Technet page...

Link to article...

You can run this script using Windows Task Scheduler to send an email notification of the status of the SQL services...
SELECT dss.[status], dss.[status_desc]
FROM   sys.dm_server_services dss
WHERE  dss.[servicename] LIKE N'SQL Server Agent (%';

or you can also look in sys.databases and check the value in state column

Value | Applies to

0 = ONLINE |

1 = RESTORING |

2 = RECOVERING | SQL Server 2008 through SQL Server 2016

3 = RECOVERY_PENDING | SQL Server 2008 through SQL Server 2016

4 = SUSPECT |

5 = EMERGENCY | SQL Server 2008 through SQL Server 2016

6 = OFFLINE | SQL Server 2008 through SQL Server 2016

7 = COPYING | Azure SQL Database

10 = OFFLINE_SECONDARY | Azure SQL Database
Avatar of dbaSQL

ASKER

I should have also said that we already have a powershell report which returns the service state -- but that is run on a scheduled basis.  I need something that alerts me when the servcies stop.  Not something that tells me about it after the fact.  You understand what I'm saying?
Avatar of dbaSQL

ASKER

>>SELECT dss.[status], dss.[status_desc]
FROM   sys.dm_server_services dss
WHERE  dss.[servicename] LIKE N'SQL Server Agent (%';

If the services are not running, I am not going to get into the system to run any type of query.
Well, I know that if you're on Windows 2008 Server, you can set up Task Scheduler to send an email based on an Event Log entry. You could, I suppose setup such an Event-driven task to send an email when that event is written to the windows event log.
Here's an article on how to do that... then you just need to know the event ID for when that service stops...

Link to article...
Did you try creating a SQL job run it every 10 sec and setup email notification to report if the server state changes?
Avatar of dbaSQL

ASKER

Jeff, it's Windows Server 2003 still.  Do you think this approach will still work?
neehar, sys.databases.state column is the status of the database.  That is not what I am looking for.  Further, if I creae a job to run every  X minutes, and send an email if sys.database.state <> online -- the job isn't going to run with the server's services stop.
Yes, it should for 2003. The event-triggered thing has been around for a while... it's newer versions of Windows Server (after 2008R2) which have taken it away (or at least, made it more complicated to use).
2003 also has a tool called Eventtriggers.exe which you could possibly use for that type of alert. It's been a very long time since I've done anything with that though.
Here's a link I found on how to do this using eventtriggers.exe...  Link to article...
Avatar of dbaSQL

ASKER

Thank you, Jeff.  I am reading your suggested reference now.  Seems feasible, but still reading.
Avatar of Jake Callander
Jake Callander

Zabbix appliance is quick and easy to set up and has that Win Service monitoring built into the zabbix agent.... plus so much more server monitoring. Could set it up to have an email or text sent out as well.

https://www.zabbix.com/documentation/3.2/manual/appliance
SpiceWorks has a free monitoring tool.  You can set it up to send emails when certain conditions occur.  It also can be used to monitor remote servers.
Spiceworks is a great tool with a knowledgeable community, I love them. Depending on the nature of your job and how many systems you have to manage.  Atera service can monitor SQL services and much more but with a price.   https://www.atera.com/
Avatar of dbaSQL

ASKER

still working this.  will send status as soon as I can.
Avatar of dbaSQL

ASKER

apologies for the late response.  still working on this, but jeff, I wanted to say that event triggers suggestion seems the most viable.  And, I could use it for disk space shortage notifications and service state alerts.  Would you agree?
ASKER CERTIFIED SOLUTION
Avatar of Jeff Edmunds
Jeff Edmunds
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of dbaSQL

ASKER

I haven't gotten it together yet, Jeff, but I wanted to go ahead and award the points and close the inquiry.  This is the solution I will be using.  Thank you for the input!
Side note:  scheduling alerting tasks using task scheduler is prone to silent failures.  If the task fails, no one knows unless they go in and check task scheduler results.
A better approach would be to trigger Exception alerting from within the task, so that if something internally bombs in the task, it doesn't fail silently.  Then schedule the task using something that will alert you in the event that the task fails to run.  Task Scheduler won't do that.