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.