January 9, 2009 at 5:33 pm
Comments posted to this topic are about the item To Ping SQL Servers
January 16, 2009 at 2:51 am
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
January 16, 2009 at 5:45 am
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
January 16, 2009 at 6:16 am
David that's great! I'll give it a try as well.:w00t:
January 16, 2009 at 9:28 am
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?
February 4, 2009 at 4:30 pm
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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy