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

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

3.29 (7)

You rated this post out of 5. Change rating

Share

Tags

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

3.29 (7)

You rated this post out of 5. Change rating

Related content