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