August 23, 2014 at 10:29 am
I HAVE TO WRITE ONE STORE PROCEDURE THAT EVERY MIN IT HAS TO CHECK MY SQL INSTANCE IS UP AND RUNNING IF NOT IT HAS TO SEND MAIL. PLEASE HELP ME HOW TO WRITE
August 23, 2014 at 10:48 am
rajeshjaiswalraj (8/23/2014)
I HAVE TO WRITE ONE STORE PROCEDURE THAT EVERY MIN IT HAS TO CHECK MY SQL INSTANCE IS UP AND RUNNING IF NOT IT HAS TO SEND MAIL. PLEASE HELP ME HOW TO WRITE
If your SQL Server instance is down, it won't execute a stored proc. Or is it running on a different instance?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
August 23, 2014 at 11:00 am
JUST FOR EXAMPLE - i have two SQL server instance A and B.
From B i have to check A Instance is up or not. If not it has to send mail
August 23, 2014 at 11:46 am
Duplicate post. You have one in a SQL Server 2012 forum and another in a SQL Server 2014 forum?? What version of SQL Server are you running? Also, please don't double post questions. Most people who help monitor the Active Threads page and it shows new posts to threads on all forums.
August 23, 2014 at 4:34 pm
rajeshjaiswalraj (8/23/2014)
I HAVE TO WRITE ONE STORE PROCEDURE THAT EVERY MIN IT HAS TO CHECK MY SQL INSTANCE IS UP AND RUNNING IF NOT IT HAS TO SEND MAIL. PLEASE HELP ME HOW TO WRITE
No need to SHOUT;-)
Quick thought, what you need is a monitoring application/system not an SQL Server stored procedure, that is simply the wrong tool. There are lots of monitoring applications available, you could even roll your own using i.e. SSIS and a simple connection/trivial query execution or even a scheduled job with SQLCMD.
😎
August 23, 2014 at 6:07 pm
Eirikur Eiriksson (8/23/2014)
rajeshjaiswalraj (8/23/2014)
I HAVE TO WRITE ONE STORE PROCEDURE THAT EVERY MIN IT HAS TO CHECK MY SQL INSTANCE IS UP AND RUNNING IF NOT IT HAS TO SEND MAIL. PLEASE HELP ME HOW TO WRITENo need to SHOUT;-)
Quick thought, what you need is a monitoring application/system not an SQL Server stored procedure, that is simply the wrong tool. There are lots of monitoring applications available, you could even roll your own using i.e. SSIS and a simple connection/trivial query execution or even a scheduled job with SQLCMD.
😎
Actually, as I mentioned on the other thread the OP started, I used just such a process to monitor a production server at a previous employer that had a habit of randomly rebooting and sometimes not coming back up when it did. I offered to include our Sys Admins on the notification but was told that Operations had a system that monitored all the servers and alerted the ops when a server was down.
One weekend I started getting pages from my server that the production server was down. After the first 3 alerts over 7 minutes, I started receiving alerts every 3 minutes. I let this go all day until about 5:00 PM when I called our Sys Admin at home and asked if he had gotten any calls about the server. His reply was no and I told him that it has been down for over 12 hours. Needless to say he wasn't too happy with ops. He also wasn't too happy with me, but then I told him that he said Ops had a system to tell them when a system was down. The following Monday I also showed him all the email alerts I received, one for each page I received.
Normally, when I received the 3rd page in 7 minutes I would call Ops myself and tell them that server needed to be rebooted. Once my pages stopped going off, I knew the server was back up and operational.
After fighting with the sys admins about the server (the lead insisted it was SQL Server causing the random reboots) we moved everything to a spare server and the reboot problem went away. Turns out there was a defect on the server motherboard. I kept telling them it wasn't SQL Server.
August 24, 2014 at 12:38 am
Lynn Pettis (8/23/2014)
Eirikur Eiriksson (8/23/2014)
rajeshjaiswalraj (8/23/2014)
I HAVE TO WRITE ONE STORE PROCEDURE THAT EVERY MIN IT HAS TO CHECK MY SQL INSTANCE IS UP AND RUNNING IF NOT IT HAS TO SEND MAIL. PLEASE HELP ME HOW TO WRITENo need to SHOUT;-)
Quick thought, what you need is a monitoring application/system not an SQL Server stored procedure, that is simply the wrong tool. There are lots of monitoring applications available, you could even roll your own using i.e. SSIS and a simple connection/trivial query execution or even a scheduled job with SQLCMD.
😎
Actually, as I mentioned on the other thread the OP started, I used just such a process to monitor a production server at a previous employer that had a habit of randomly rebooting and sometimes not coming back up when it did. I offered to include our Sys Admins on the notification but was told that Operations had a system that monitored all the servers and alerted the ops when a server was down.
One weekend I started getting pages from my server that the production server was down. After the first 3 alerts over 7 minutes, I started receiving alerts every 3 minutes. I let this go all day until about 5:00 PM when I called our Sys Admin at home and asked if he had gotten any calls about the server. His reply was no and I told him that it has been down for over 12 hours. Needless to say he wasn't too happy with ops. He also wasn't too happy with me, but then I told him that he said Ops had a system to tell them when a system was down. The following Monday I also showed him all the email alerts I received, one for each page I received.
Normally, when I received the 3rd page in 7 minutes I would call Ops myself and tell them that server needed to be rebooted. Once my pages stopped going off, I knew the server was back up and operational.
After fighting with the sys admins about the server (the lead insisted it was SQL Server causing the random reboots) we moved everything to a spare server and the reboot problem went away. Turns out there was a defect on the server motherboard. I kept telling them it wasn't SQL Server.
This can and often will put one on the spot as being the bearer of bad news.;-)
😎
August 24, 2014 at 1:49 pm
Closing this thread.
Please post replies here: http://www.sqlservercentral.com/Forums/Topic1606742-3411-1.aspx
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply