locked
Need to configure SQL Server Ping alert (SQL 2008) RRS feed

  • 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

    Sunday, March 4, 2012 9:25 AM

Answers

  • 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/

    • Proposed as answer by Naomi N Tuesday, March 6, 2012 2:46 PM
    • Marked as answer by KJian_ Monday, March 12, 2012 5:36 AM
    Tuesday, March 6, 2012 12:43 PM
    Answerer

All replies

  • 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/

    • Proposed as answer by Uwe RickenMVP Sunday, March 4, 2012 9:49 AM
    Sunday, March 4, 2012 9:47 AM
    Answerer
  • 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

    Tuesday, March 6, 2012 12:35 PM
  • 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/

    • Proposed as answer by Naomi N Tuesday, March 6, 2012 2:46 PM
    • Marked as answer by KJian_ Monday, March 12, 2012 5:36 AM
    Tuesday, March 6, 2012 12:43 PM
    Answerer
  • Hi Uri..

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


    india

    Tuesday, March 6, 2012 2:20 PM