Technical Article

Ping Linked Servers From Agent Job

,

This script is meant to be inserted into an agent job and scheduled every 3-5 minutes for execution.  The job will ping your active linked servers for query response.  If the ping fails, the script will send you an email with a server down alert.

Steps to use:

  1. Ensure DB mail is setup with a profile and update @profile_name in the script
  2. Review the code above and update with your email address or tweaks
  3. Add/remove the linked servers on instance and update to reflect the active servers you want to ping
  4. Create SQL Server agent job and schedule

* Note that error handling has been added for error 258, which is a timeout error.  The script will re-attempt the ping 10 times if it hits a timeout error before it will send you an email.  This is to prevent unwanted emails during heavy load times\disk imaging operations.

IF OBJECT_ID('tempdb..#LinkedServerTest') IS NOT NULL
BEGIN
DROP TABLE #LinkedServerTest
END;

CREATE TABLE #LinkedServerTest ( -- If ping fails this info will be emailed
Server_Name NVARCHAR(256),
[STATUS] CHAR(7),
[Time_Server_Polled] DATETIME,
[ERROR_NUMBER] INT,
[ERROR_MESSAGE] VARCHAR(MAX)
)

IF EXISTS (
SELECT 1
FROM master.sys.servers
WHERE is_linked = 1
)

BEGIN
DECLARE @LinkedServer NVARCHAR(256)
DECLARE @TimeoutCount INT
SELECT @LinkedServer = MIN(NAME)
FROM master.sys.servers
WHERE is_linked = 1

WHILE @LinkedServer IS NOT NULL
BEGIN
SET @TimeoutCount = 0
RETRY:
BEGIN TRY
DECLARE @Str NVARCHAR(256)
SELECT @Str = N'EXEC(''SELECT @@SERVERNAME'') AT ' + @LinkedServer
EXEC sp_executesql @Str
END TRY
BEGIN CATCH
IF (ERROR_NUMBER() <> 258 OR @TimeoutCount = 10) -- Timeout Error (happens early AM during disk imaging)
BEGIN
INSERT INTO #LinkedServerTest
SELECT @LinkedServer AS Server_Name,
'OFFLINE' AS [STATUS],
GETDATE() AS [Time_Server_Polled],
ERROR_NUMBER() AS [Error_Number],
ERROR_MESSAGE() AS [ERROR_MESSAGE]

-- Email server down alert
DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)

SET @xml = CAST(( SELECT [Server_name] AS 'td','',[STATUS] AS 'td','',
   [Time_Server_Polled] AS 'td','',[ERROR_NUMBER] AS 'td','',[ERROR_MESSAGE] AS 'td'
FROM #LinkedServerTest
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(255))
SET @body ='<html><body><H3>Executed as agent job "DBA Maintenance: Ping DB Servers" on YOURSERVERHERE</H3>
<table border = 1> <center>
<tr>
<th> Server Name </th> <th> Status </th> <th> Time </th> <th> Error # </th> <th> Error Message </th>
</tr>'    

SET @body = @body + @xml + '</center></table></body></html>'

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DB Mail', -- Update with your email profile
@body = @body,
@body_format = 'HTML',
@recipients = 'SQLnotify@domain.com', 
@subject = 'DB Server Down Alert!';
GOTO JUMPLOOP -- Go to Label JUMPLOOP
END
IF ERROR_NUMBER() = 258 -- Timeout Error
BEGIN
SET @TimeoutCount = @TimeoutCount + 1
WAITFOR DELAY '00:00:00.10' -- Wait for 10 ms
GOTO RETRY-- Go to Label RETRY
END
ELSE GOTO JUMPLOOP -- Go to Label JUMPLOOP
END CATCH
JUMPLOOP:
SELECT @LinkedServer = MIN(NAME)
FROM master.sys.servers
WHERE is_linked = 1
AND NAME > @LinkedServer
END -- End of WHILE loop
END

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

(7)

You rated this post out of 5. Change rating

Share

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

(7)

You rated this post out of 5. Change rating

Related content