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?


2 Answers 2


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.

  • +1 as that's an excellent reply unfortunately though not the answer. I have no msaccess processes open. I do a copy of the db down to my location which ensures the SQL Server is the only connection.
    – Lumpy
    Commented Feb 4, 2014 at 14:02
  • 1
    Ahh, the 0%, 0s one. Unfortunately, the only way I've ever been able to get rid of that is through offlining. ALTER DATABASE [Catalog] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; then ALTER DATABASE [Catalog] SET MUTLI_USER; seems to be the least destructive for this.
    – Avarkx
    Commented Feb 4, 2014 at 14:23
  • Unfortunately the linked server doesn't seam to like that command. I get an error: User does not have permission to alter database, the database does not exist, or the database is not in a state that allows access checks.
    – Lumpy
    Commented Feb 4, 2014 at 14:43
  • Sorry, I was referring to the catalog on the SQL Server 2008 R2 instance; the one with the hung spids ( dbid of 153 ), not the linked server. Either way, it's not much of an anwser, tbh.
    – Avarkx
    Commented Feb 4, 2014 at 14:46
  • Sorry I should have guessed that. It's something I hadn't thought to try but I can't take that db offline during regular hours. Thanks though.
    – Lumpy
    Commented Feb 4, 2014 at 15:17

We have a similar issue and only work around we could see is a instance restart as the Killed/Rollback session does not clear.

In my case though , the Linked Server works fine for 'sa' but hangs only for the account from which the Killed/Rollback session comes in. Now it has been identified as Known Issue and App team is investigating on the sessions that gets killed from their end as it is not something done by DBA but App internal.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.