<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Quickly Disconnect Users From a Database

Published on
9,559 Points
3,559 Views
Last Modified:
Approved
Disconnect All Users of a Database

There comes a time in every DBA’s life when he or she has many users connected to a database that needs to be detached, placed in single user mode, or simply refreshed.  The problem is they keep reconnecting more quickly than you can KILL them off.  This is especially true when you are working in an environment where the lines between testing and production are blurred.

Hopefully you know about the KILL command, and aren’t thinking I’m advocating becoming a serial killer.  Though a hatchet properly placed can cure a lot of problems, especially when you can get to the network cable!

To disconnect a user you can use sp_who2 and look at the users currently connected to the database in question.  Once you have the spid for the connection, you can issue the command:

KILL spid

The problem will be, you have to look at all the connections that are currently open, KILL those connections, then check sp_who2 again to see if any new connections were opened.  Then lather, rinse, repeat.  This process is tedious at best.

There is a better way.
The Solution

CREATE PROCEDURE kill_database_users @dbname sysname with recompile

AS

declare @spid smallint

declare @msg    varchar(255)

declare @dbid int

select

        @dbid = sdb.dbid

from    master..sysdatabases sdb

where   sdb.name = @dbname

declare db_users insensitive cursor for

select

        sp.spid

from    master..sysprocesses sp

where   sp.dbid = @dbid

open db_users

fetch next from db_users into @spid

while @@fetch_status = 0

        begin

        select @msg = 'kill '+convert(char(5),@spid)

        print @msg

        execute (@msg)

        fetch next from db_users into @spid

        end

close db_users

deallocate db_users

GO

This Should close out any connections to the @dbname in question.  Once it’s complete, you can then switch to single-user mode, or detach the database, or whatever else you may need to do.  Normally, I'd avoid a cursor, but this is one of those special cases where you want to sequentially work through a list, and perform a function.  That's strictly the domain of a cursor!

Enjoy!
0
0 Comments

Featured Post

IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month