To Ping SQL Servers

  • Hotfix

    SSC-Addicted

    Points: 449

    Comments posted to this topic are about the item To Ping SQL Servers

  • liebesiech

    SSCrazy

    Points: 2468

    Actually a good and simple way to check if your servers are still reachable as part of your daily DBA routine or if somebody reports a problem.:P

  • David Chance

    Valued Member

    Points: 73

    We have serveral servers, both SQL Servers and others. Since SQL Server, especially 2005 and newer, do email so well I have two of my SQL Servers, so if one is down the other still functions, read a list of servers from a table and ping them. If they don't respond I read from a list of people from a table and email them.

    -- Pings servers listed in a table, if no contact emails people

    Declare @ServerName as nchar(20), @EmailPerson nchar(40), @result int,

    @CmdTxt varchar(100), @Comment varchar(1000)

    DECLARE Servers CURSOR FOR

    SELECT ServerName

    FROM dba..ServersToPing

    ORDER BY ServerName

    OPEN Servers

    FETCH NEXT FROM Servers

    into @ServerName

    WHILE @@FETCH_STATUS = 0

    BEGIN

    set @CmdTxt = 'ping ' + ltrim(rtrim(@ServerName))

    EXEC @result = xp_cmdshell @CmdTxt, no_output

    IF (@result = 1)

    begin

    Set @Comment = 'Server ' + char(34) + ltrim(rtrim(@ServerName)) + char(34) +

    ' - Contact failure, possibly not running'

    Print @Comment

    declare EmailList cursor for

    select EmailAddress from dba..EmailAddresses

    open EmailList

    fetch next from EmailList

    into @EmailPerson

    WHILE @@FETCH_STATUS = 0

    BEGIN

    -- Be sure and make sure the appropriate profile_name is used for

    -- server this code is being run on.

    EXEC msdb.dbo.sp_send_dbmail

    @profile_name = 'in-mslc-sql3 mail',

    @recipients = @EmailPerson,

    @body = @Comment,

    @subject = @Comment

    fetch next from EmailList

    into @EmailPerson

    end

    CLOSE EmailList

    DEALLOCATE EmailList

    end

    FETCH NEXT FROM Servers

    into @ServerName

    END

    CLOSE Servers

    DEALLOCATE Servers

    GO

  • liebesiech

    SSCrazy

    Points: 2468

    David that's great! I'll give it a try as well.:w00t:

  • dherman

    SSC Journeyman

    Points: 93

    These are great thanks to all. We run on a clustered server. Anyone know how to set an alert to determine when the SLQ has failed over to the other node?

  • Mohamed-401375

    SSC Eights!

    Points: 965

    Good tip. Thanks,

    We can have a utility that parses the output file looking for the text - 100% loss.

    That indicates that a server is not responding and if so take appropriate action.

Viewing 6 posts - 1 through 6 (of 6 total)

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