SQL SERVER – 2005 Take Off Line or Detach Database « Journey to SQL Authority with Pinal Dave

SQL SERVER – 2005 Take Off Line or Detach Database

April 25, 2007 by pinaldave

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 (http://blog.SQLAuthority.com) , BOL

Posted in Pinal Dave, SQL, SQL Authority, SQL Performance, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology | 14 Comments

14 Responses

on April 25, 2007 at 9:40 am | Reply Glenn Berry

Nice post, with some useful samples.
on August 24, 2007 at 7:10 am | Reply SQL SERVER - 2005 - T-SQL Script to Attach and Detach Database Journey to SQL Authority with Pinal Dave

[...] Reference : Pinal Dave (http://www.SQLAuthority.com), SQL SERVER – 2005 Take Off Line or Detach Database [...]
on October 20, 2007 at 11:14 pm | Reply Carlos

And SQL sERVER 6.5? wATH
on February 17, 2008 at 4:28 am | Reply Magnus

Hi Dave!

ALTER DATABASE [mydb] SET OFFLINE WITH ROLLBACK IMMEDIATE

Works just fine but if I want to take many DBs (354) offline with a script at the same time. Can that be done. I also what to exclude some DBs from beeing taken offline.

/Magnus
on April 25, 2008 at 5:20 am | Reply RichSeattle

Magnus, sure it can. A quick way to do this is simply to use a cursor and a query similar to -

select name from sysdatabases where name not in (‘master’, ‘msdb’, ‘model’, ‘tempdb’, ‘donttakeoffline’)

*note ‘donttakeoffline’ is the name of a db you might want to exclude, the others you shot NOT take offline)

Have the cursor dynamically build the command and exec such as this –

set @cmd = ‘Alter database ‘ + @dbname + ‘ set offline with rollback immediate’
exec (@cmd)

If you want to exclude them you can exclude from the original query for the cursor or use an IF statement example

If @dbname != ‘dontoffline’
begin
exec @cmd
else
end

Mind you, doing this can be dangerous and is not 100% certain. Most things like this should be done one at a time and/or monitored very closely.

If I were going to do this, I would have it record into a table somewhere of the status of taking it offline and when it was taken offline for each DB.

*not responsible for people breaking their DB using this information*
on April 28, 2008 at 10:57 am | Reply Harinath_hyd

Hi dave
i want to lock the record and send the message to user who try to access the same record for certain time
can u help me out .
on July 9, 2008 at 11:06 am | Reply Andy Loh

how to shrink transaction loh in sql 2005.
on October 17, 2008 at 5:14 pm | Reply Dinesh

Hi Pinal,

1.Can we shrink the log file after taking database into offline?
on December 10, 2008 at 9:51 pm | Reply Fabio

Hello,
does a offline database uses less memory ?
on February 24, 2009 at 12:53 pm | Reply komninos

hi i want to know how i can create an offline server to play for example Lineage-i own SQL and navicat
on February 24, 2009 at 12:55 pm | Reply komninos

i want to know how i can create an offline server to play for example Lineage-i own sql and navicat
on March 5, 2009 at 12:57 am | Reply jelong

Carlos,
for SQL Server 6.5, you can use sp_dboption dbname, offline, true
on October 15, 2009 at 2:52 pm | Reply riyo

I tried to Drop database from web page, by executing SQL Command, then show the result, done or error. And I got this message : Cannot drop database “detach” because it is currently in use.

Thanks for your help.
on December 1, 2009 at 2:00 pm | Reply Shafi

I assume using the command -

ALTER DATABASE [mydb] SET OFFLINE WITH
ROLLBACK AFTER 30 SECONDS

will close the existing connections as I want to restore my DB and restore command fails with

“Exclusive access could not be obtained because the database is in use.”

Any help will be appreciated.
Thanks