Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer 1

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.

share|improve this answer
    
+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 Feb 4 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 Feb 4 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 Feb 4 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 Feb 4 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 Feb 4 at 15:17
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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