Sql-server – SQL Query Killed, remains in Kill/Rollback with estimated time increasing

sql server

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)

Best Answer

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/

Related Question