question about sql server database roll back

Question

I use sp_who2 and find one process is rolling back. The process takes very long long time.

Can I use sql commadn kill to kill this process? Not sure because it is rolling back now. Looks very time consuming.....

Or restart sql server services to kill this process? Want to end this time consuming process as soon as possible.

Another question:

Can insert/delete when database is rolling back...?

Answer 1

Killing the process is not going to change things. The ROLLBACK must be performed to being the database to a consistent state.

Restarting SQL Server will change things a little. The process will be gone, but the rollback still needs to be done. It will be performed during recovery, and it can often be faster. But it can also mean that it takes a long time before the database comes online.

Right now, the database is accessible, but locks related to the transaction being rolled back, can block other processes.

A special situation is when someone gets the idea to kill a process that is stuck somewhere outside SQL Server. That is, in a Windows API call or access to a linked server. Because the process is executing outside SQL Server it never gets to know that it is killed. (SQL Server uses co-operative multitasking. There is no kernel that can evict threads.) In situations like this, restarting SQL Server is often the only way out. (Unless you can the the process on the remote server and kill that one.)

Answer 2

Hi @bestrongself ,

When the process is rolling back, suggest you do not restart the SQL server. Stopping a long running rollback by restarting SQL server (or rebooting the server itself) will only cause you to expend more time than you already have. The transactions have to be rolled back. If you do try the restart method your SQL Server (all other databases) will be unavailable until the rollback completes.

Suggest you waiting for the process rolling back. Rolling back make the database to be consistent.

> Can insert/delete when database is rolling back...?

No, you can’t. The database is not accessible when database is rolling back.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".