There are few options after setting the database offline.
Alter database set offline
This is the most basic form for the syntax.
alter database "AdventureWorks" set offline
This statement would attempt to set the database in the restrict mode but would wait any active processes to complete. This includes if user just simply run “use database” or just there is the internal processes “AUTO_UPDATE_STATISTICS_ASYNC”. If this database option “AUTO_UPDATE_STATISTICS_ASYNC” is set, it is possible to block the alter database command.
To check whether the AUTO_UPDATE_STATISTICS_ASYNC is set, we can not find it from sp_helpdb or sp_dboption. We need to check the sys.database table.
ALTER DATABASE "AdventureWorks2" SET AUTO_UPDATE_STATISTICS ON
ALTER DATABASE "AdventureWorks2" SET AUTO_UPDATE_STATISTICS_ASYNC ON
select name,is_auto_update_stats_on,is_auto_update_stats_async_on from sys.databases where name='AdventureWorks2'
If there is active user connection block the command ( we can find it base on sp_who), just kill the session, and the alter database would continue to bring the database offline.
alter database set offline with no_wait
alter database "AdventureWorks2" set offline with no_waitWhen use with NO_WAIT option, the alter database command will fail with error if there is any active user connection. So the command would not be hang forever.
ALTER DATABASE SET OFFLINE WITH ROLLBACK IMMEDIATE
alter database "AdventureWorks2" set offline WITH ROLLBACK IMMEDIATE
This would terminate all the user connection, processes and set the database offline.