SQL SERVER – Difference Between ROLLBACK IMMEDIATE and WITH NO_WAIT during ALTER DATABASE

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

Solarwinds

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

SQL SERVER - Difference Between ROLLBACK IMMEDIATE and WITH NO_WAIT during ALTER DATABASE alterdb2

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

SQL SERVER - Difference Between ROLLBACK IMMEDIATE and WITH NO_WAIT during ALTER DATABASE alterdb1

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:

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, , ,
Previous Post
SQL SERVER – Quick Note of Database Mirroring
Next Post
SQL SERVER – Mirroring Configured Without Domain – The server network address TCP://SQLServerName:5023 can not be reached or does not exist

Related Posts

19 Comments. Leave new

  • johnjakubowski
    March 17, 2010 8:32 am

    It looks like those images should be switched.

    Reply
  • 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

    Reply
  • 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 ;)

    Reply
    • 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

      Reply
  • 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.

    Reply
    • 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

      Reply
      • hi pinaldave sir,
        At wich situation we use this statements
        ROLLBACK IMMEDIATE and WITH NO_WAIT during ALTER DATABASE
        mY mailid [email removed]
        am waiting for ur reply
        Thanks and regards
        Ramakrishna

  • Jim Christmas
    March 14, 2012 9:10 pm

    Pinaldave,

    Thank you for the article. Where in the interface can I see the current settings for each database?

    Thank you,
    Jim Christmas

    Reply
  • how to use rollback in sql server 2005 if i have delete all record from table how to retrieve all value again in a same table

    Reply
  • Hi pinal,
    where it is used in the rollback statement and what is usage of this statement..

    Reply
  • Hi, I got this scenario…
    I call an SP that loop through a service table for create a dinamic SQL for ALTER VIEW, FROM table “A_Products” to “B_Products”…etc…

    I try to do this operation with less rumors underlayer for not create deadlock victims…

    Lately when try to do #2 ALTER my SP catch errors and my production DB (DB1) remains in single user mode.

    Is there another way for optimize this switch from “tables A” to “tables B”?
    Many times all is gone but when something wrong is a problem ’cause my production DB1 remais in SINGLE USER MODE.

    Thanks for advises
    —————————–.

    ALTER DATABASE DB1 SET SINGLE_USER WITH ROLLBACK IMMEDIATE

    –Sometis here i got error deadlock victims and DB1 remains in SINGLE_USER MODE
    ALTER DATABASE DB2 SET SINGLE_USER WITH ROLLBACK IMMEDIATE

    BEGIN TRANSACTION @MyTran;

    WHILE @MIO_ID >0
    BEGIN
    SELECT @table_name_to = TABLE_NAME_TO
    FROM DB1.dbo.service_import02
    WHERE MIO_ID = @MIO_ID

    EXEC(‘ALTER VIEW ‘+ @ActualSchemaIs + ‘.’+@Prefix_View + @table_name_to + ‘ AS
    SELECT *
    FROM ‘+ @DB_name + ‘.’ + @ActualSchemaIs + ‘.’ + @Prefix_New + @table_name_to
    )

    SET @MIO_ID = @MIO_ID-1
    END — WHILE @MIO_ID >0

    IF @@error = 0
    BEGIN
    COMMIT TRANSACTION @MyTran;

    ALTER DATABASE DB1 SET MULTI_USER

    ALTER DATABASE DB2 SET MULTI_USER

    Reply
  • I tested on my computer, and found both of the codes will not generate error. The error you received is that you have set the DB to single_user, you tried to set it as single_user again.

    Reply
  • i’m wrong, you are right

    While we are using ALTER DATABASE, we can use any of the options “ROLLBACK IMMEDIATE” or “WITH NO_WAIT”.

    When we use “ROLLBACK IMMEDIATE”, it rollbacks other transactions which are running currently in database and successfully execute “ALTER DATABASE” command.

    When we use “WITH NO_WAIT”, it kills the “ALTER Database” transaction itself, if any other transactions are running currently in database.

    We can specify time (in seconds) also for Rollback, “ROLLBACK AFTER 100″. This will wait until 100 seconds to complete other transactions. After 100 seconds, it kills the other transactions and successfully executes “ALTER DATABASE” command.

    Reply
  • Dear Sir,
    Can u explain what and all the Process are there in “ALTER DATABASE” Command?

    Reply
  • Andreas Meyer zu Driehausen
    December 26, 2016 3:54 pm

    Hi,
    I understand it this way:

    with ROLLBACK Immediate –> cancels all current transactions and enforces the Alter Database statement

    with NO_Wait –> cancels the Alter Database Statement itself and allows the transactions to occur and take their time.

    So the funny part is the name “No_Wait”, as actually it gives the current transactions all the time they need.
    So there is a mental switch here, which initially is hard to follow. Rollback Immediate cancels outstanding transactions where No_wait refers to the alter db statement and tells it: should there be outstanding transactions, forget this alter db statement – just leave it immediately, don’t bother.

    So the bottom line is: they cannot be used both at the same time, right?
    At least I couldn’t. Only one will work at a time.

    Sometimes there are funny twists in IT and it’s quite a struggle to get these simple things straight.

    Thank you
    Andi

    Reply
  • Very valid point @Andi.

    Reply

Leave a Reply

Menu