Solved

sp_detach_db

Posted on 2004-09-21
7
2,615 Views
Last Modified: 2012-08-13
Hi,
i need to detach database using osql, even if it's in use.
when detaching database from EM, window appears with option to clear current connection.
how can this be done using T-sql?

0
Comment
Question by:ill
7 Comments
 
LVL 2

Expert Comment

by:vidnan123
ID: 12110471
Use the following SQL syntax.

sp_detach_db

For example,

EXEC sp_detach_db 'pubs', 'true'

This statement detatches the pubs database with the option to not 'update statistics' after detaching the database

0
 
LVL 12

Author Comment

by:ill
ID: 12110488
i need to close acive connections first. that's the problem.
--------------
Server: Msg 3701, Level 16, State 1, Line 1
Cannot detach the database 'rbweb2' because it is currently in use.
0
 
LVL 6

Assisted Solution

by:OlegP
OlegP earned 50 total points
ID: 12110563
for killing use

DECLARE @Proc varchar(30)

DECLARE Processes CURSOR FOR
SELECT prc.spid
FROM master.dbo.sysprocesses prc
INNER JOIN master.dbo.sysdatabases db
      ON prc.dbid = db.dbid
WHERE
      db.name = ' HERE DB NAME'

OPEN Processes
FETCH NEXT FROM Processes INTO @Proc

WHILE @@FETCH_STATUS = 0
BEGIN
      SET @Proc = 'KILL ' + @Proc
      EXEC(@Proc)
      FETCH NEXT FROM Processes INTO @Proc
END

CLOSE Processes
DEALLOCATE Processes
0
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 
LVL 26

Accepted Solution

by:
Hilaire earned 50 total points
ID: 12110581
You could also do it as follows

alter database <dbname> set single_user with rollback immediate

<....... do your stuff here ..........>
-- also make sure to issue a "use master" before calling sp_detach

alter database <dbname> set multi_user with rollback immediate
0
 
LVL 11

Expert Comment

by:ram2098
ID: 12110584
This is what the SQL Server also internally do when you execute "clear" command..so, to script it..it is like this... (Example for PUBS database)
But, unless it is very much required. I do not recommend to query the system tables directly.


use master
GO

declare @spid int

declare curprocesses cursor
for select spid from master.dbo.sysprocesses WHERE (dbid = (SELECT dbid FROM master.dbo.sysdatabases WHERE (name = 'pubs')))

open curprocesses
fetch next from curprocesses into @spid

while (@@fetch_status = 0)
  begin
   exec ('kill ' + @spid)
   fetch next from curprocesses into @spid
  end

close curprocesses
deallocate curprocesses

EXEC sp_detach_db 'pubs'

0
 
LVL 12

Author Comment

by:ill
ID: 12110614
@oleqP thanks, it works
@Hillaire ellegant :)
0
 

Expert Comment

by:intermediagroup
ID: 28893144
if using management studio take the database offiline first.
0

Featured Post

Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Server maintenance plan 8 53
Connect remotely to SQL Server DB without TCP/IP enabled? 2 28
Need SSIS project 2 23
Access #Deleted data 20 36
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question