Today, we are going to discuss about something very simple, but quite commonly confused two options of ALTER DATABASE.
The first one is 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 explaination, 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 DATABASE … ROLLBACK rollbacks those incomplete transaction immediately, where as ALTER DATABASE … NO_WAIT will terminate and rollback the transaction of ALTER DATABASE … NO_WAIT 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.
Reference : Pinal Dave (http://blog.SQLAuthority.com)
pinaldave
It looks like those images should be switched.
Thanks for note, Yes, Images needed to switch.
Now the images are corrected.
Hi Pinal,
Just to get this clear, in case of the NO_WAIT option, if there are incomple transactions in the database, then the NO_WAIT will error out?
Thank you
Hi Ramdas,
That is my understanding. If the transactions are not complete by the time No_wait is executed they error out.
Thank you pinal for the clarification.
Nice to be back here commenting
so Pinal, what’s your recommendation method?
I almost always use WITH ROLLBACK IMMEDIATE since I don’t want to wait for others :)
NO WAIT makes me wait on others by failing ;)
Hello Jerry,
Welcome back.
I usually give some time for trans to rollback. So I usually use with rollback with sometime delay of 30 seconds.
Kind Regards,
Pinal
Books online says –
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, the request will fail.
Pinal did you mean to say this ?
1. If you dont specify any option – Alter database will wait for transactions to complete and then execute
2. If you specify rollback- alter database, will rollback all transaction immediately and then effect the alter database changes
3. If you specify NO_WAIT, dont wait for transaction to complete, try executing my alter db statement, and let the alter fail for its specified exception. This wouldnt have been the case for the 1. one. it would have waited for the transact to complete and then the alter could succeed or fail based on the context of alter.
Hello Vinod Sir,
This is great observation and I am fortunate that you are here to help me clear this doubt further.
Yes, 1 and 3 are same but in case of the 1st, it will wait for the transactions to complete. Many time, it waits for long time and times out. In case of 3, if Alter is not executed right away, it fails immediately.
Kind Regards,
Pinal