Script to check SQL Server Linked Server Status

 

It leverages on the system stored procedure sys.sp_testlinkedserver

SET NOCOUNT ON; DECLARE @LinkName NVARCHAR (128), @retval INT, @msg varchar(300) IF (SELECT COUNT (srvname) FROM sys.sysservers WHERE srvname <> @@SERVERNAME) = 0 RETURN DECLARE srvname INSENSITIVE CURSOR FOR         (SELECT srvname FROM sys.sysservers WHERE srvname <> @@SERVERNAME) FOR READ ONLY OPEN srvname     FETCH NEXT FROM srvname INTO @LinkName WHILE @@FETCH_STATUS = 0 BEGIN     BEGIN TRY         EXEC @retval = sys.sp_testlinkedserver @LinkName     END TRY         BEGIN CATCH         IF @LinkName IS NULL RETURN         SET @retval = SIGN(@@ERROR)         IF @retval <> 0         BEGIN           SET @msg = 'Unable to connect to the Linked server : ' + @LinkName           RAISERROR (@msg, 16, 2 )         END     END CATCH     FETCH NEXT FROM srvname INTO @LinkName     END        CLOSE srvname DEALLOCATE srvname