Solved

Force disconnect all users through query analyzer?

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

Turn Insights Into Action

You’ve already invested in ITSM tools, chat applications, automation utilities, and more. Fortify these solutions with intelligent communications so you can drive business processes forward.

With xMatters, you'll never miss a beat.

Question has a verified solution.

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

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 …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
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.

718 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