To Ping SQL Servers

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Mahidhar Vattem » To Ping SQL Servers

To Ping SQL Servers

Rate Topic

Display Mode

Topic Options

Author

Message

Hotfix

Posted Friday, January 09, 2009 5:33 PM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 03, 2013 12:54 PM
Points: 34, Visits: 635

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

Post #634017

liebesiech

Posted Friday, January 16, 2009 2:51 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 10, 2013 3:55 AM
Points: 126, Visits: 751

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

David Chance

Posted Friday, January 16, 2009 5:45 AM

Forum 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

liebesiech

Posted Friday, January 16, 2009 6:16 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 10, 2013 3:55 AM
Points: 126, Visits: 751

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

Post #637942

dherman

Posted Friday, January 16, 2009 9:28 AM

SSC 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

Mohamed-401375

Posted Wednesday, February 04, 2009 4:30 PM

SSC Eights!

Group: General Forum Members
Last Login: Monday, November 26, 2012 5:36 PM
Points: 869, Visits: 96

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 »

Permissions