Solved

sp_detach_db

Posted on 2004-09-21
7
2,604 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
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.

828 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