Feb 13

Difference Between ALTER DATABASE WITH ROLLBACK IMMEDIATE and WITH NO_WAIT

For quite some time I didn’t know the difference between ROLLBACK IMMEDIATE and NO_WAIT when altering a database. These are similar in nature, but perform very different tasks. Lets look at the differences:

  • ROLLBACK IMMEDIATE
      Specifies whether to roll back after the 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, the request will fail.

If at first glance you still don’t understand the difference don’t fret…I didn’t either. Here is the major difference:

ROLLBACK IMMEDIATE will take all uncommitted transactions and running queries and roll them back immediately. NO_WAIT is a more polite method; it will wait for transactions to commit or roll back and running queries to complete before the ALTER DATABASE statement executes.

You can read more about the differences on Microsoft’s MSDN site:
http://msdn.microsoft.com/en-us/library/bb522682.aspx.
Definitions above are taken from there.