FAQ : What is WITH ROLLBACK ROLLBACK IMMEDIATE | AFTER integer [SECONDS] | NO_WAIT in ALTER DATABASE in SQL Server

When you ALTER a database state what to do with the current transaction(s) (If any) on the database are defined by these statements.

WITH ROLLBACK IMMEDIATE

WITH ROLLBACK IMMEDIATE tell the DB engine to rollback all the transaction immediately and make the DB into the given state in the Alter Statement. Ie
In my database, there is a transaction running for last one hour and it is not yet committed and now I want to make this database into SINGLE_USER. The following line of Alter statement will do that for me
-- Set the database to single user
Alter Database MyDatabase set SINGLE_USER WITH ROLLBACK IMMEDIATE

The above statement will rollback the current transaction immediately and bring the DB to Single_USER

WITH ROLLBACK AFTER integer [SECONDS]

This command tells DB engine to wait for the specified seconds before rolling back all the transaction. If the transaction still not committed in the given seconds then rollback.

ALTER DATABASE MyDatabase SET SINGLE_USER WITH ROLLBACK AFTER 30

The above statement will bring the database to SINGLE_USER after 30 sec. If any transaction is there after 30 sec it will rollback.


WITH NO_WAIT
This is bit different from the above two statements. This tells the DB engine to stop or terminate ALTER Statement rather than terminating or rollback the transaction. Ie. If any active transaction is there on the database, the ALTER Statement will fail.

ALTER DATABASE MyDatabase SET SINGLE_USER WITH NO_WAIT