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

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.

Read more: Journey to SQL Authority with Pinal Dave

Posted via email from jasper22's posterous