2

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."

Micky McQuade
  • 195
  • 9

2 Answers2

3

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...

gbn
  • 6,009
  • 1
  • 17
  • 21
  • 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 01:07
0

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.

MicSim
  • 136
  • 1
  • 1
  • 7