Solved

Force disconnect all users through query analyzer?

Posted on 2004-10-08
2
4,791 Views
Last Modified: 2011-09-20
Hi,

I am writing a sql script to build a database.  Usually I can just "execute" the entire script which begins with this:
USE [MASTER]
GO
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'MyDatabase')
      DROP DATABASE [MyDatabase]
GO

This tends to always work when I first open query analyzer.  However after I mess around with the script enough, eventually it stops working with error:
Server: Msg 3702, Level 16, State 4, Line 6
Cannot drop the database 'MyDatabase' because it is currently in use.

No one else is connected to this database.  The only "fix" I know of is to open enterprise manager and go to detach the database (gives me option to punt all users).  I punt the users but do not detach database and then the script can be executed.

So - my question is:  Is there a SQL query that I can place at the top of this script (before DROP DATABASE) that will punt all the users and save me this hassle?

Thanks,
Ian
0
Comment
Question by:ibost
2 Comments
 
LVL 32

Assisted Solution

by:bhess1
bhess1 earned 25 total points
ID: 12264049
I use this procedure before attempting to restore a DB in an automated process.  It kills off all connections to the specified DB:

Create Procedure usp_KillConnectionsOnDB
@dbName varchar(40)
AS

Create Table #tmpKill(Cmd varchar(200))

Declare @LoopCount int
Declare @cmd varchar(200)
Declare @rows Int
Declare @dbid Int

Select @dbid = dbid From master.dbo.sysdatabases
WHERE [Name] = @dbName   --  This is the name of the DB, e.g. 'pubs'

Set @LoopCount = 0

CheckKill:

Insert Into #tmpKill (Cmd)
Select 'Kill ' + Cast(spid as varchar)
FROM master.dbo.sysprocesses
WHERE dbid = @dbid
AND RTrim(status) = 'sleeping'

Set @Rows = @@RowCount

If @Rows <> 0
BEGIN
      Declare c_KillMe CURSOR For
            Select cmd From #tmpKill

           Open c_Killme
           Fetch Next From c_Killme Into @Cmd
           While @@FETCH_STATUS = 0
           BEGIN
               Exec (@cmd)
               Fetch Next From c_Killme Into @Cmd
           END
           Close c_Killme
           Deallocate c_Killme
END

-- Now, check to see if an active process is working on the DB.  If so, try to wait until it is
--    complete (makes the kill process more polite).

If Exists (Select spid FROM master.dbo.sysprocesses
WHERE dbid = @dbid)
BEGIN
      If @LoopCount < 30      -- Max 30 second wait
      Begin
            Waitfor Delay '000:00:01'
            Set @LoopCount = @LoopCount + 1
            Truncate Table #tmpKill
            Goto CheckKill
      End
      Else
      Begin   --- Eventually, just kill the proc -- we have a job to do.

            Truncate Table #tmpKill
            Insert Into #tmpKill (Cmd)
                  Select 'Kill ' + Cast(spid As varchar)
                  FROM master.dbo.sysprocesses
                  WHERE dbid = @dbid

            If @@Rowcount <> 0
            BEGIN                  
                  Declare c_KillMe CURSOR For
                        Select cmd From #tmpKill
                  Open c_Killme
                  Fetch Next From c_Killme Into @Cmd
                  While @@FETCH_STATUS = 0
                  BEGIN
                        Exec (@cmd)
                        Fetch Next From c_Killme Into @Cmd
                  END
                  Close c_Killme
                  Deallocate c_Killme
            END
      End
End
Drop Table #tmpKill

GO




To use it, place the sproc in the master DB, connect to the master DB, and

Exec usp_KillConnectionsOnDB 'MyDBName'
0
 
LVL 34

Accepted Solution

by:
arbert earned 100 total points
ID: 12265002
Personally, I like to make it easier than that:

alter database yourdatabase set read_only with rollback immediate   --this disconnects all users
go
alter database yourdatabase set read_write with rollback immediate
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Suggested Solutions

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now