Today, we are going to discuss something very simple, but quite commonly confused two options of ALTER DATABASE. Difference Between ROLLBACK IMMEDIATE and WITH NO_WAIT during ALTER DATABASE.
The first one is the ALTER DATABASE …ROLLBACK IMMEDIATE and the second one is WITH NO_WAIT.
Many people think they are the same or are not sure of the difference between these two options. Before we continue our explanation, let us go through the explanation given by Book On Line.
ROLLBACK AFTER integer [SECONDS] | ROLLBACK IMMEDIATE
Specifies whether to roll back after a specified number of seconds or immediately.
NO_WAIT
Specifies that if the requested database state or option change cannot complete immediately without waiting for transactions to commit or roll back on their own, then the request will fail.
If you have understood the difference by now, there is no need to proceed further. If you are still confused, continue with the rest of the post.
There is one big difference between ROLLBACK and NO_WAIT. In case incomplete Transaction ALTER DB … ROLLBACK rollbacks those incomplete transactions immediately, whereas ALTER DB … NO_WAIT will terminate and rollback the transaction of itself.
I think it can be clearly explained with the help of the following images.
Option 1: ALTER DATABASE … ROLLBACK
Connection 1 – Simulating some operation using WAITFOR DELAY
WAITFOR DELAY '1:00:00'
Connection 2
ALTER ;DATABASE TestDb SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
Option 2: ALTER DATABASE … NO_WAIT
Connection 1 – Simulating some operation using WAITFOR DELAY
WAITFOR DELAY '1:00:00'
Connection 2
ALTER DATABASE TestDb SET SINGLE_USER WITH NO_WAIT;
Let me know if this example was simple enough. I would love to get your feedback in the comments area about this subject. Here are a few additional blog posts you can read on this topic:
- SQL SERVER – Delay Function – WAITFOR clause – Delay Execution of Commands
- SQL SERVER – Database Testing and Waitfor Delay – Notes from the Field #099
- SQL SERVER – Find Statistics Update Date – Update Statistics
- SQL SERVER – Delay Command in SQL Server – SQL in Sixty Seconds #055
Reference: Pinal Dave (https://blog.sqlauthority.com)
