1

I killed a transaction and now it remains in Killed/Rollback status and whenever I run Kill 64 with statusonly I get this output:

SPID 64: transaction rollback in progress. Estimated rollback completion: 56%. Estimated time remaining: 12860 seconds.

However it stays at 56% and the estimated time only increases. It has been this way for a few hours atleast.

The query that I killed was dbcc shrinkdatabase(new2012, 10)

  • This is not a light operation. The session will remain live and kicking while the rollback finishes its job. You can't really do much to make it faster. – Marian Jan 23 '13 at 8:56
2

How large is your database? It's not uncommon for rollback transactions to take considerably longer than the operations they are trying to do, especially if affected objects have dependencies.

Another important question is what environment are you running this on? I'm assuming you're not running it on live, which leads to a couple more questions, specifically: -How long can you afford to wait for it? -Can you kill the database and restore a backup?

If you can't wait for an long or potentially unknown amount of time, your best bet might be simply restoring a backup. If you don't want to do that, you might want to wait for a while longer. You can use the sys.dm_exec_requests DMV to get information on wait times and whether or not it's being blocked, and on what might be blocking it.

SELECT *
FROM sys.dm_exec_requests

After you find columns you might find useful, you can revise the select statement to get rid of the spam.

For more information, see this post by Paul Randal (SQL MVP and author) on the shrink command: http://www.sqlskills.com/blogs/paul/why-lob-data-makes-shrink-run-slooooowly-t-sql-tuesday-006/

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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