Fix Linked Server Without Restarting SQL Server Instance

Question

Problem: Occasionally a nightly job will fail, and when I look into it, it is the result of a query across a linked server failing. But for some reason that query seems to remain 'open' in some way and cause subsequent failures.

Bad Solution: The only way I have found to fix this is to restart the instance which seems to clear everything up. But when it happens in production, obviously it is a bit of a hassle.

Question: Does anyone have strategies for resetting a linked server which is stuck open? I am not even really sure what is happening under the hood.

Other Checks made: -

• Tested the query on the server linked to. And I can select the data fine on that side
• Tested everything else on the server w/ the link. Everything works fine there it is just that when it tries to query across that link it fails.

Is the only solution to this to really restart the instance?

• SQLChao: When you say the query is failing, how so? Like a timeout? Syntax?
• TheNorthWes: There wasn't any error message from the job. The query just hung and was awaiting a select to return something. The job started, fired a select... and it seems like the linked server froze up for lack of a better word for it. Nothing was returned and the job just kept waiting. Even when the job is killed and started again something is still alive in that linked server keeping it things frozen.
• TheNorthWes: @JChao sorry, sort of described that poorly. The job was executing and when the SPID is killed it doesn't properly free up the linked server. It results in the job not even be able to be started again because the allegedly dead job is still holding onto something. SQL seems to think it is still alive and executing for eternities.
• DenisT: @AdmiralAdama, can we see the query?
• TheNorthWes: @DenisT it is really just a select * from table. It runs fine most of the time but this happens occasionally on a few different jobs.

Answer 1

Depending on the statement you are running and how you have your linked servers configured, you could be running into a situation where you have an orphaned distributed transaction sitting out there that you need to kill. To identify if this is the case or not, first run the following query:

SELECT DISTINCT request_owner_guid
FROM sys.dm_tran_locks 
WHERE request_session_id = -2

If this returns records, chances are good that you are in fact running into this very situation. To kill these orphaned transactions, you have to run the following statement for each record returned by the query above:

KILL 'UOW'

UOW is the request_owner_guid returned in the above query.

I've run into this situation with both linked server issues and jobs that are making calls externally from the SQL Server process. In either case, I would find that an external process would interfere (e.g. network drop, process is killed or errors unexpectedly, etc.) and then the transaction wouldn't properly terminate at the SQL Server. SQL Server won't know that the transaction has actually failed and things will just sit out there until you either walk through this process or bounce the server as you are already doing now.

Hope that helps.

Answer 2

unchecked "Allow in Process" property of the provider

Articles Permissions needed to set up linked server with out-of-process provider :http://blogs.msdn.com/b/dataaccesstechnologies/archive/2010/08/19/permissions-needed-to-set-up-linked-server-with-out-of-process-provider.aspx

Answer 3

Use TCPView (https://learn.microsoft.com/en-us/sysinternals/downloads/tcpview) to find and kill/close the bad connection. You can find some more information here: http://www.jaygeiger.com/index.php/2015/03/03/how-to-kill-a-frozen-linked-sql-server-connection/