Posted by: Denny Cherry
Based on some questions on forums and some of the responses that people have been giving there appears to be done misunderstanding about what the WITH ROLLBACK IMMEDIATE option does when added to the ALTER DATABASE command. My goal here is to try and clear up at least some of the confusion.
When you run the ALTER DATABASE command without a termination clause (ROLLBACK AFTER, ROLLBACK IMMEDIATE, NO_WAIT) the ALTER DATABASE command will run until the command completes, or until if command is terminated.
The ROLLBACK AFTER command tells the SQL Server to cancel any pending transactions and rollback those transactions after N number of seconds.
ALTER DATABASE YourDatabase ... WITH ROLLBACK AFTER 30
The ROLLBACK IMMEDIATE command tells the SQL Server that if it can’t complete the command right away, then the other pending transactions should be rolled back.
The NO_WAIT command has the opposite effect as ROLLBACK IMMEDIATE. If the ALTER DATABASE transaction can’t be completed then that transaction is terminated.
ALTER DATABASE YourDatabase ... WITH NO_WAIT
Hopefully this helps clear up some of the confusion.