Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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

EXAMPLE USE 1

 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: 1481 | Views in the last 30 days: 11
 
Related Articles
FORUM

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

FORUM

Where is the SQL Server project template?

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

FORUM

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...

FORUM

Delete ALL records where table join produces values

where a record is returned

FORUM

Where To Create/Modify Database Maintenance Plans

Confused as to where to create DB Maint Plans

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones