Alter Database Set Options Using NO_WAIT,ROLLBACK IMMEDIATE,ROLLBACK AFTER

When altering database SET options such as snapshot isolation,read committed snapshot, we can specify options like No_wait;Rollback Immediate;Rollback after n

Example:
ALTER DATABASE AdventureWorks2012
SET SNAPSHOT ISOLATION ON WITH ROLLBACK IMMEDIATE

Rollback Immediate – Rollbacks all the uncommitted transactions immediately and then alters the database setting.

Example:
ALTER DATABASE AdventureWorks2012
SET SNAPSHOT ISOLATION ON WITH ROLLBACK AFTER 10

ROLLBACK AFTER N – Waits for N seconds and then rollbacks all the uncommitted transactions after that,in the example,it will wait for 10 seconds and rollbacks all the uncommitted transactions and then alters the database setting.

Example:
ALTER DATABASE AdventureWorks2012 SET SNAPSHOT ISOLATION ON WITH NO_WAIT

NO_WAIT – Will try to alter the database immediately and will fail, if it could not apply them.

STANDARD ALTER COMMAND – We can also write the Alter Database syntax without mentioning the above options but behaves similar to NO_WAIT option.

You can read more in this MSDN article.