Need to configure SQL Server Ping alert (SQL 2008)

Question

Hi,

In current env we are facing problem with SQL Server 2008 ....Windows Server goes to hung state & all the Application Batch job (sql server) are getting fail bcoz application team cant connect to sql server from front end. and same time if we try to server we are able to ping not able to take remote session.then finally we do hard reeboot and restart sql service post all jobs executes successfully.

So i need to configure alert from other server (SQL Server) when sql server is not able to access from other server (Sql Service) we should get the alert to our mail. do we have idea about this ? 

Hint: i cross checked when windows server went to hung state sql server was not accesible (not able to connect thru sqlwb/ssms from other server) from other server (sql server).

---

india

Answer 1

What error do you get?

set nocount on
CREATE TABLE  #t_ip (ip varchar(255))
DECLARE  @PingSql varchar(1000)
SELECT @PingSql = 'ping ' + '10.10.5.2'
INSERT INTO  #t_ip EXEC master.dbo.xp_cmdshell @PingSql
SELECT * FROM #t_ip
IF  EXISTS (SELECT TOP 2 * FROM #t_ip WHERE IP =  'Request timed out' )
BEGIN
             DROP TABLE #t_ip
            RETURN 
END
 DROP TABLE #t_ip

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 2

Hi Uri,

Above script i know but for this i need to configure job that run and ping every 10 or 15 min and we will be getting alert.

i need script so no need to configure job..that will ping to server and incase ping will not happen then we willl be getting alert.

---

india

Answer 3

I hope you understand that without schedule you won't get alert.. Have a look at Windows Task scheduler if you avoid using SQL Agent ..BTW I see no reason why not having a job that run every 10-15 minutes to ping the server....

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 4

Hi Uri..

I am done it...thanks for your support...

---

india