Sql-server – linked server hanging

linked-serverms accesssql-server-2008-r2

We've been using a linked server to an access database for close to a year now with little issue. Now when I run a select query against the linked server the query sits there and never finishes. Killing the session results in the familiar transaction sitting in Rollback/Killed status until the service is restarted. 

(SPID 153: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds.)

Is there a way to clear out these select statements or perhaps run them such that they don't end up in the rollback/killed status?

Has anyone else seen this sort of issue before? What did you do to get around it?

Best Answer

I believe I've encountered this sort of problem before, primarily in dealing with Excel. If it's anything like what I'm referring to, it's probable that during your connection to the Access database you are receiving some form of UI prompt or otherwise "waiting for input" type of event. The root cause of this could be a number of things, but most likely is that the Access database is being held open by a separate process and the linked server connection attempt is being "warned" about it.

When you run KILL against the spids on the SQL Server 2008 R2 server, I expect you get something along the lines of:

Estimated rollback completion: 100%. Estimated time remaining: 0 seconds.

If that's the case, you will be able to clear these "ghost" sessions by also terminating the pid on the other side of the connection. Go to the server where the Access database exists and run a tasklist command from the console ( or use the task manager or whatever makes you happy ). If you have a number of these "ghost" spids, you'll likely notice a number of Access processes as well ( MSACCESS.EXE, [pid], blah blah ). You can remove these processes by using taskkill /IM "MSACCESS.EXE" /F from the console, or if you're feeling cautious, taskkill /PID [pid] /F. The termination of these destination processes should allow the "ghost" spids on the server to resolve.

Assuming this is the problem, you'll probably find that your SELECT commands will function as they have in the past as well.

Related Question