EXEC sp_dboption N'mydb', N'offline', N'true'
OR
ALTER DATABASE [mydb] SET OFFLINE WITH
ROLLBACK AFTER 30 SECONDS
OR
ALTER DATABASE [mydb] SET OFFLINE WITH
ROLLBACK IMMEDIATE
Using the alter database statement (SQL Server 2k and beyond) is the preferred method. The rollback after statement will force currently executing statements to rollback after N seconds. The default is to wait for all currently running transactions to complete and for the sessions to be terminated. Use the rollback immediate clause to rollback transactions immediately.
Reference : Pinal Dave (https://blog.sqlauthority.com) , BOL
25 Comments. Leave new
Great little post. I find myself coming back here every time I need to take a database offline :)
Does anyone know what options are used if you use the UI to take the DB offline?
Thanks, excellent!!
No Need to do anything just kill the process SqLWB.exe FROM TAsK MANAGER and open sql server and right click on the database and take offline, If it doesn’t work then after session is being killed type the command ALTER DATABASE SET OFFLINE WITH ROLLBACK IMMEDIATE and then offline. It will work as it worked for me as well.