Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

To Ping SQL Servers Expand / Collapse
Author
Message
Posted Friday, January 9, 2009 5:33 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 10:03 AM
Points: 34, Visits: 644
Comments posted to this topic are about the item To Ping SQL Servers
Post #634017
Posted Friday, January 16, 2009 2:51 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, June 29, 2014 11:44 PM
Points: 130, Visits: 801
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
Post #637822
Posted Friday, January 16, 2009 5:45 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 22, 2012 11:31 AM
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
Post #637915
Posted Friday, January 16, 2009 6:16 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, June 29, 2014 11:44 PM
Points: 130, Visits: 801
David that's great! I'll give it a try as well.
Post #637942
Posted Friday, January 16, 2009 9:28 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, January 11, 2011 11:37 AM
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?
Post #638234
Posted Wednesday, February 4, 2009 4:30 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, July 1, 2013 4:39 PM
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.

Post #650473
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse