PING all the Linked Servers and get a status report

On 03 Jun, 2015

MS SQL DBA

By : Charith Silva

No Comments

As a DBA it’s handy to have a script to PING all the Link servers and get a report to examine the connectivity. Following T-SQL script will ping all the preconfigured linked servers and get the packets sent, received and lost details.

DECLARE @SQL NVARCHAR(MAX);
 
IF OBJECT_ID('tempdb..#Ping_Results') IS NOT NULL
DROP TABLE #Ping_Results
 
CREATE TABLE #Ping_Results
(
[Server_Name] NVARCHAR(128),
[PING_Results] NVARCHAR(4000),
)


DECLARE #servers CURSOR FOR
SELECT DISTINCT  [Linked_Server_Name] 
FROM 
(
SELECT SUBSTRING(name,0,CHARINDEX('\',name)) AS [Linked_Server_Name]
FROM master.sys.servers
WHERE name like '%\%'

UNION 

SELECT name
FROM master.sys.servers
WHERE name NOT like '%\%'
)LinkedServers

 
DECLARE @LinkedServerName varchar(128)
 
OPEN #servers
FETCH #servers INTO @LinkedServerName
WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @SQL = 'declare @results table(result varchar(4000));'
SELECT @SQL = @SQL +' insert into @results'
SELECT @SQL = @SQL +' EXEC master.dbo.xp_cmdshell ''ping '+@LinkedServerName+''''
SELECT @SQL = @SQL +' INSERT INTO #Ping_Results([Server_Name],[PING_Results])'
SELECT @SQL = @SQL +' select '''+@LinkedServerName+''',result  from @results WHERE result LIKE ''%Packets: Sent%'''


PRINT @SQL
EXEC(@SQL)
FETCH #servers INTO @LinkedServerName
END
 
CLOSE #servers
DEALLOCATE #servers
 
 
SELECT [Server_Name],[PING_Results],
CASE 
WHEN [PING_Results] LIKE '%Lost = 0 (0% loss)%' THEN 'Ping Succeeded' 
ELSE 'Failed' 
END AS [Status]
FROM #Ping_Results
ORDER BY [Server_Name]
 
DROP TABLE #Ping_Results

DECLARE @SQL NVARCHAR(MAX);

IF OBJECT_ID('tempdb..#Ping_Results') IS NOT NULL

DROP TABLE #Ping_Results

CREATE TABLE #Ping_Results

(

[Server_Name] NVARCHAR(128),

[PING_Results] NVARCHAR(4000),

)

DECLARE #servers CURSOR FOR

SELECT DISTINCT [Linked_Server_Name]

FROM

(

SELECT SUBSTRING(name,0,CHARINDEX('\',name)) AS [Linked_Server_Name]

FROM master.sys.servers

WHERE name like '%\%'

UNION

SELECT name

FROM master.sys.servers

WHERE name NOT like '%\%'

)LinkedServers

DECLARE @LinkedServerName varchar(128)

OPEN #servers

FETCH #servers INTO @LinkedServerName

WHILE @@FETCH_STATUS = 0

BEGIN

SELECT @SQL = 'declare @results table(result varchar(4000));'

SELECT @SQL = @SQL +' insert into @results'

SELECT @SQL = @SQL +' EXEC master.dbo.xp_cmdshell ''ping '+@LinkedServerName+''''

SELECT @SQL = @SQL +' INSERT INTO #Ping_Results([Server_Name],[PING_Results])'

SELECT @SQL = @SQL +' select '''+@LinkedServerName+''',result from @results WHERE result LIKE ''%Packets: Sent%'''

PRINT @SQL

EXEC(@SQL)

FETCH #servers INTO @LinkedServerName

END

CLOSE #servers

DEALLOCATE #servers

SELECT [Server_Name],[PING_Results],

CASE

WHEN [PING_Results] LIKE '%Lost = 0 (0% loss)%' THEN 'Ping Succeeded'

ELSE 'Failed'

END AS [Status]

FROM #Ping_Results

ORDER BY [Server_Name]

DROP TABLE #Ping_Results

About The Author

Charith Silva

Charith Silva is a Microsoft certified SQL Server developer and database administrator who was graduated at Buckinghamshire New university in the UK. His career was started in 1998, primarily into Web application development, and later diversified into database development. He has got a vast experience in SQL Server database development, Database administration and Business Intelligence development. He believes that sharing the knowledge is key to the success.

Number of Posts : 87

All Posts by : Charith Silva