SQL SERVER – 2005 Take Off Line or Detach Database

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

Solarwinds
Previous Post
SQL SERVER – TRIM() Function – UDF TRIM()
Next Post
SQL SERVER – ERROR Messages – sysmessages error severity level

Related Posts

25 Comments. Leave new

  • Niels Brinch
    June 21, 2012 6:19 pm

    Great little post. I find myself coming back here every time I need to take a database offline :)

    Reply
  • Does anyone know what options are used if you use the UI to take the DB offline?

    Reply
  • Thanks, excellent!!

    Reply
  • 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.

    Reply

Leave a Reply

Menu