Get an alert when SQL Server Services stop

Question

Get an alert when SQL Server Services stop

gary1

SSC-Insane

Points: 21152
More actions
October 9, 2010 at 10:38 am

#239507

Hi,
We have SQL Server 2005 and configured log shipping. On Secondary servers, somehow the SQL Server Services got stopped and we did not notice for 2 days!:hehe:
I have configured database mail to get email notification when Log shipping threshold exceeds and when the log shipping jobs fail but they are of NO use when the SQL Server services were NOT running.
So is there any script that monitor the SQL Server Services Status daily and send an email when the Services were stopped?
Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

free_mascot One Orange Chip Points: 27169 More actions · Answer 1

You can write scripts on OS level to monitor the services and if stoped send message through netsend to monitoring desktop.

You need to have third party tool like MOM, redgate etc. to monitor services.

HTH

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

eshueshwar Old Hand Points: 380 More actions · Answer 2

If you can't afford for the third party tools.. Here the idea on which you need to work on writing a windows script.

Within the sys process when ever the sql agent services were running we could find the entry...

application_name = 'SQLAgent - Generic Refresher '

In short you can connect to the master database & run the query within the sysprocess.. by this way we can ensure that both SQL & SQL server agents were running.

SkyBVI SSCoach Points: 15128 More actions · Answer 3

Also,

You can check the sql servers services running status by just pinging them from cmd

telnet sqlservername 1433

If a blank screen follows, that means services are running, otherwise it will tell that it couldn't connect to the sql server.

It takes hardly 1 min to check the connectivity of sql servers, in case you can't get scripts....

Regards,

Sushant

Regards
Sushant Kumar
MCTS,MCP

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 4

There are many ways of accomplishing this.

I would probably look into either powershell or telnet (or combination) to verify a service is running. You could probably go retro and do it in command shell too 🙂

paul.knibbs SSCoach Points: 15320 More actions · Answer 5

We monitor all services using Nagios--it has the advantage of being free, but the disadvantage of being primarily for the Linux platform and thus a bit tricky to set up if you have no experience of that sort of thing!

S. Kusen SSChampion Points: 10936 More actions · Answer 6

We have an administration server, and we maintain a list of the SQL Servers we support. We have an SSIS periodically connect to every instance in the list to validate connectivity. If there's no response, then we get an email or a ticket generated to us for review.

We classify our systems into 3 categories: 1)Mission Critical, 2) Production, non-critical, and 3) non-production.

For our mission critical DB's, we test connectivity every minute to each individual DB we label critical.

For our prod non-critical, we test connectivity to master DB every 9 minutes and run a simple (select DB_ID()) statement because we are only looking for a response back. (This particular simple check would be how you'd know your services are not running on one of your instances)

For our non-production, we test connectivity 3 times a day.

Hope that helps,

Steve

gary1 SSC-Insane Points: 21152 More actions · Answer 7

hi s.k

Could you please share your monitoring scripts for mission critical servers and from where they scheduled?

thanks