Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have a job that usually runs for about 2 hours. This morning it was still running at 5 hours and I decided to kill it. It has been in a "KILLED/ROLLBACK" status for quite a while now. When I run the KILL command again to get the status of the rollback, I get the following:

SPID 84: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds.

Is there anyway I can boost priority or tell SQL Server to "hurry up"? :)

Update:

Or is there a way for me to say "I don't care about the state of the table. Just quit."

share|improve this question

migrated from stackoverflow.com Sep 10 '10 at 2:20

This question came from our site for professional and enthusiast programmers.

add comment

2 Answers

up vote 0 down vote accepted

Prior to SQL Server 2008 I don't know of any possible solution. Since SQL Server 2008 there is the Resource Governor which allows you to specify workload and resources used. I haven't used it yet, so I can't tell you any more details.

share|improve this answer
add comment

Not at all.

One option is to stop SQL Server, move the MDF and LDF files, start SQL Server, delete the suspect DB, reattach the MDF file and hope your DB still works.

Or restore it.

I'm not kidding: ACID forces the rollback and won't allow a corrupt table without outside intervention.

There is a chance it could be blocked for another reason but generally it's rolling back a transaction. A SQL Server restart could help in this case but don't come crying if it goes wrong :-)

If this is production, then wait or delete/restore. You can't say "I don't care about the state of the table. Just quit." in production...

share|improve this answer
1  
I ended up restarting the SQL Service. The database was In Recovery for about 10 minutes and then all was well. The table in question really is just a status table that doesn't matter much at all. It can be rebuilt easily. I understand what you are saying though. Thanks! –  Micky McQuade Sep 10 '10 at 1:07
add 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.