Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


To Ping SQL Servers


To Ping SQL Servers

Author
Message
Hotfix
Hotfix
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 651
Comments posted to this topic are about the item To Ping SQL Servers
liebesiech
liebesiech
SSC-Enthusiastic
SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)

Group: General Forum Members
Points: 146 Visits: 854
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.Tongue
David Chance
David Chance
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 61
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
liebesiech
SSC-Enthusiastic
SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)

Group: General Forum Members
Points: 146 Visits: 854
David that's great! I'll give it a try as well.w00t
dherman
dherman
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 50
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
Mohamed-401375
SSC Eights!
SSC Eights! (889 reputation)SSC Eights! (889 reputation)SSC Eights! (889 reputation)SSC Eights! (889 reputation)SSC Eights! (889 reputation)SSC Eights! (889 reputation)SSC Eights! (889 reputation)SSC Eights! (889 reputation)

Group: General Forum Members
Points: 889 Visits: 97
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search