?
Solved

Force disconnect all users through query analyzer?

Posted on 2004-10-08
2
Medium Priority
?
4,801 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 32

Assisted Solution

by:Brendt Hess
Brendt Hess earned 100 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 400 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

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

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.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

752 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