SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Ping Linked servers

By Mark Huber,

I created a monitoring server and one of the things I want to know if all the servers that I look after are up and running. So I created this proc to do just that. I even have a linked server that is an Oracle box and I pinged that one to ensure I can still get a connection to it.

I wanted to only know when any of the linked servers where having issues so this is how this was created. I added parameters and logic to handle the situation where you want to know the the job was run and what it's findings were.

I will proably add the the sql to write this out to a table so we can record system uptime over time and automatically calculate database availability % and report on that SLA .


To execute from a SSMS


 DECLARE @return_value int

 EXEC @return_value = [dbo].[usp_lsping]

 @nf = N'Y',

 @wtn = N'E'

 SELECT 'Return Value' = @return_value

I set this up as a job to run at regular intervals ie dev/test servers every 30 min every 6 minutes for production boxes I want it to let know me if it finds an error. I added an in clause to the where clause that builds the list of the servers so that one version check the dev and test versions and another checks the production databases.

 Please feel free to update and modify as you see fit please contribute it back so we can get a good script to use and share with everyone.

Total article views: 1555 | Views in the last 30 days: 2
Related Articles

Controlling DB View's return dataset using WHERE Clause?

This is a query on how to control DB View's return dataset using WHERE Clause for desired output


Where is the SQL Server project template?

Where is the SQL Server project template? I need to create a CLR stored procedure!


How to create a function that takes a table name and where clause as parameters

How to create a function that takes a table name and where clause as parameters and returns the numb...


Delete ALL records where table join produces values

where a record is returned


Where To Create/Modify Database Maintenance Plans

Confused as to where to create DB Maint Plans