Solved

Force disconnect all users through query analyzer?

Posted on 2004-10-08
2
4,792 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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

929 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

11 Experts available now in Live!

Get 1:1 Help Now