This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(73.60000000000001, 91%, 17%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EK%3C/text%3E%3C/svg%3E)
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.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(160, 61%, 25%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ESC%3C/text%3E%3C/svg%3E)
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
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.
And if what Tom says does not work out:
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!
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.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(268.8, 52%, 36%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EI%3C/text%3E%3C/svg%3E)
By default SQL Server will wait up to 20 minutes after a shutdown request as it tries to rollback any transactions. If the rollback is still in still happening after 20 minutes: SQL Server will give up and shutdown anyway.
You can make it shutdown immediately cleanly using:
SHUTDOWN WITH NOWAIT;
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(67.2, 1%, 16%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ETP%3C/text%3E%3C/svg%3E)
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.
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.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(192, 23%, 28%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EJW%3C/text%3E%3C/svg%3E)
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.
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.
Hi @knt1N ,
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.
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.