question

knt1N-9820 avatar image
0 Votes"
knt1N-9820 asked knt1N-9820 commented

How to force a rollback of SQL Server to stop

I'm registering or updating a lot of data in DB in SQL Server.
Due to circumstances, this transaction is a very large unit, and if an error occurs, a rollback of more than 24 hours occurs.
In this situation, is there an emergency way to force the rollback to stop?

・"SQL Server 2017 Standerd" is used.
・This DB is for verification only, and I do not care if the data is damaged. The DB recovery model is "simple", and I restore it using a DB backup I made before. In the worst case, reinstall SQL Server itself.

I'd appreciate it if someone could answer my questions.

sql-server-general
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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

0 Votes 0 ·

Hello.
Thank you for your answer.

I think what you say is right, too. I think should make the transaction smaller.
However, in some cases, this may not be possible.
For example, Always Encrypted is one of the features provided by SQL Server.
For data updates using these features, developers do not have control over the units of transactions.

Even so, we had better not force a rollback stop on the server that is actually operating.
Since this DB was used for verification purposes only, I ended up asking questions of irregular means.

Thank you again for your advice.

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered knt1N-9820 commented

And if what Tom says does not work out:

  1. Stop SQL Server.

  2. Delete the files for the database.

  3. Start SQL Server.

  4. Drop the database.

I am not fully sure that the last step will work, but if it does not: create a new database, stop SQL Server and copy the files of the new database to the place of the old database.

But be very careful when you play this game, so that you don't delete database files you really want to keep!



· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello.
Thank you for your answer.

I'm glad I got more information.
If you need this method, I'd like to do it with caution.

Thank you again for your advice.

0 Votes 0 ·
TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered knt1N-9820 commented

If you don't care about damaging the database, you can try restarting the SQL Server service (sometimes twice) and it may stop the rollback and mark the database as "suspect" Then you can drop the database.

I do not recommend doing that normally or on a production server.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello.
Thank you for your answer.

This time, I was just looking for emergency measures on the verification server, so I will be careful not to take such a method on a normal server.

Thank you again for your advice.

0 Votes 0 ·
JeffreyWilliams-3310 avatar image
0 Votes"
JeffreyWilliams-3310 answered knt1N-9820 commented

You may have better luck modifying the process to batch the changes - instead of trying to perform everything in a single batch that has to be rolled back. You also may find that batching the changes performs much better than a single transaction.

This way - if an error occurs - only the latest batch has to roll back and that will be much smaller in scope and impact.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello.
Thank you for your answer.

As you said, if a normal server build, I think this process should be batched.
This time, we asked how to take emergency measures on a dedicated server for various load verification.

Thank you again for your advice.

0 Votes 0 ·
Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered knt1N-9820 commented

Hi @knt1N-9820,

Patience is the key in such scenarios. Rollback process to leave the database in an consistent state. First we cannot KILL a process which is in ROLLBACK state. When a process is in ROLLBACK state it has to complete its work only then it will come back to normal. If we try to restart SQL Server then recovery of the database will take time and during Rollforward-Rollback phase it will again wait for this transaction to be complete.

We can check the Percentage of ROLLBACK done using below command.

 KILL 60 WITH STATUSONLY

*where 60 is SPID which is doing ROLLBACK.

Check if this similar thread could help you.


If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.




· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello.
Thank you for your answer.

It helped me because I think I can use how to check the percentage of rollbacks in the future.

Thank you again for your advice.

0 Votes 0 ·