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.

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)

share|improve this question

migrated from stackoverflow.com Jan 22 '13 at 22:16

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

    
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

1 Answer 1

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/

share|improve this answer

Your Answer

 
discard

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