SQL Server – How to Stop Rollback Being Performed?

rollbacksql server

I have a query that takes 60 million rows, copies them in a new table, does some updates on them, then inserts them into another new table. It's all wrapped in a transaction. I thought this would be good at first, but realize it's a dumb mistake.

While it ran for 7 hours on my local instance machine, on the production DB it took over 2 days to run! Tired of waiting and realizing that my solution was probably not the best way to do this, I closed my query window and did a kill on the process. Now, it's doing a rollback.

Meanwhile, I've run this: KILL 60 WITH STATUSONLY and EXEC sp_who2 'active'. The status says "SPID 60: transaction rollback in progress. Estimated rollback completion: 62%. Estimated time remaining: 31849 seconds". The seconds and diskio is going up, so I know it's trying to do the rollback. It's extremely slow and going up a percentage complete every several hours.

My issue is that because of my actions, the database got huge and is taking up disk space. Furthermore, I don't care about these two new tables I tried to copy into. I'd like to truncate/drop them and start over with a much better method of solving this issue. However, I can't truncate/drop them in the middle of the rollback? I could care less what happens to these new tables. They are also in their own file group and .ndf files. I just want to delete these as quickly as possible so I can resume my better method. I'm on SQL 2014 and need urgent help as it's Saturday and I'd rather not bother my boss! 🙂

Best Answer

@MaxVernon already gave all the info you need. Just some addition from my side. The bigger the transaction the harder the (pre)calculation for the SQL Server. That's why your remaining time in seconds may increase while the process runs. After running a query the SQL Server can't grant more memory to the process. For big calculations your tempdb will most probably be used. And yes this will lead to a disk intensive call. That's why it's nearly always best to keep the transactions small.

Also please note that restarting SQL Server is in most cases a bad idea, since it has multiple negative side effects. It will for example rebuild the TEMP DB and also clear statistics... I

Related Solutions

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

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/

Sql-server – connection pooling, transactions, nested transaction and rollback

There is no such thing as nested transactions in any released version of SQL Server. You can say BEGIN TRANSACTION as many times as you want, but a ROLLBACK affects all of them (never mind what @@TRANCOUNT says - it only reflects nesting level).

From the documentation:

It is not legal for the transaction_name parameter of a ROLLBACK TRANSACTION statement to refer to the inner transactions of a set of named nested transactions. transaction_name can refer only to the transaction name of the outermost transaction. If a ROLLBACK TRANSACTION transaction_name statement using the name of the outer transaction is executed at any level of a set of nested transactions, all of the nested transactions are rolled back. If a ROLLBACK WORK or ROLLBACK TRANSACTION statement without a transaction_name parameter is executed at any level of a set of nested transaction, it rolls back all of the nested transactions, including the outermost transaction.

A demonstration:

CREATE TABLE dbo.foo(a INT);

BEGIN TRANSACTION;
  INSERT dbo.foo(a) SELECT 1;
  BEGIN TRANSACTION;
    INSERT dbo.foo(a) SELECT 2;
    BEGIN TRANSACTION;
    ROLLBACK TRANSACTION;

SELECT tc = @@TRANCOUNT;

SELECT a FROM dbo.foo;

DROP TABLE dbo.foo;

Results:

tc    
----
0

1 row(s) affected.

a
----

0 row(s) affected.

So, there is no way to only roll back part of a "nested" transaction - it's all or nothing. If you want previous parts of this transaction to commit even if later parts fail, then stop nesting, and commit the first transaction before starting the next one.

Best Answer

Related Solutions

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

Sql-server – connection pooling, transactions, nested transaction and rollback

Related Question