Ping Linked servers

marioblasius, 2011-11-03 (first published: 2011-10-21)

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.

Create procedure usp_lsping(@nf nchar = 'N', @wtn nchar = 'E') WITH ENCRYPTION as -- =========================================================================== -- Author:Mark Huber -- Version:1.0 -- Create date: Oct 19, 2011 -- Description:Description to ping the linked server and see if there alive -- =========================================================================== -- Parameters -- @nf - did you want to be notified Y - yes N - no -- @wtn - when to notofiy E - on error S - on Succes B - on Both -- -- EXAMPLE USE --DECLARE@return_value int --EXEC@return_value = [dbo].[usp_lsping] --@nf = N'Y', --@wtn = N'E' --SELECT'Return Value' = @return_value -- =========================================================================== -- set nocount on BEGIN DECLARE @LSrvrs TABLE ( SrvrID int IDENTITY(1,1) PRIMARY KEY, SrvName nvarchar(128) ) insert into @LSrvrs select srvname from sys.sysservers where srvname != CONVERT(nvarchar(128), SERVERPROPERTY('servername')); ; -- declare @maxloocnt int; declare @loopcnt int; declare @srvr nvarchar(128); declare @retval int; declare @Clr nvarchar(4) declare @msg nvarchar(MAX) declare @errchk int; -- set @errchk = 0; set @Clr = char(13)+char(10); select @maxloocnt = count(*) from @LSrvrs; set @loopcnt = 1; set @msg = ''; set @msg = @msg + '<Start>'+@Clr while @loopcnt <= @maxloocnt begin select @srvr = srvname from @LSrvrs where SrvrID = @loopcnt; --select @srvr begin try exec @retval = sys.sp_testlinkedserver @srvr; end try begin catch set @retval = sign(@@error); set @errchk = 99; end catch; --if @retval =qwqw 0 set @msg = @msg + @Clr+@srvr + ':' + cast(@retval as nvarchar(100)); set @loopcnt = @loopcnt + 1; end; set @msg = @msg + @Clr+'<end>'+@Clr if @nf = 'Y' begin --if @wtn = 'E' or @wtn = 'S' or @wtn = 'B' --BEGIN --print 'WTN:'+@wtn --print '@errchk:'+ CONVERT(nvarchar(128),@errchk) if (@wtn = 'S' and @errchk = 0) or (@wtn = 'E' and @errchk > 0) or(@wtn = 'B' ) BEGIN --select @msg; EXEC msdb.dbo.sp_send_dbmail @recipients=N'mark.huber@bp.com',@body=@msg, @subject ='Linked Server Ping'--,@query ='select @msg;', --@attach_query_result_as_file = 1,@query_attachment_filename ='PingResults.txt' END; --else -- BEGIN -- -- END END --end --else --BEGIN -- ''; --END; END set nocount off