Note: Along with user mode the following termination option can be specified ROLLBACK AFTER integer, ROLLBACK IMMEDIATE, NO_WAIT when changing database mode.

NO_WAIT – This option will check for connections if open before changing the state, if there are open connections it fail.

Example: Switching from MULTI_USER to SINGLE_USER
ALTER DATABASE AdventureWorks SET SINGLE_USER WITH NO_WAIT

If multiple connections are open SQL Server will return the following message:
Msg 5070, Level 16, State 2, Line 1
Database state cannot be changed while other users are using the database ‘AdventureWorks’
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.

ROLLBACK IMMEDIATE – will terminate connections immediately. All incomplete transaction will be rolled back so depending on the transaction it may take some time to terminate.

Example:
ALTER DATABASE AdventureWorks SET SINGLE_USER WITH ROLLBACK IMMEDIATE

ROLLBACK AFTER integer – Will terminate connections after the number of seconds has passed, incomplete transactions will be terminated.
ALTER DATABASE AdventureWorks SET SINGLE_USER WITH ROLLBACK AFTER integer

• Database
• SQL Server