Database In Use - Removing Users Quickly - How?

Question

Post reply

Database In Use - Removing Users Quickly - How?

jim Edwards-308192

Say Hey Kid

Points: 680
More actions
November 28, 2006 at 2:37 pm

#368091

When trying to restore our test db I get the following message: " Can not Restore, Database in Use" (abbreviated version of the real message). Is there a fast and easy way to remove all users on the db?

Thanks,
jim

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

ALTER DATABASE >DbName> SET SINGLE_USER | RESTRICTED_USER | MULTI_USER

Single_user is for one 1 connection

multi-user is to let everyone in

restricted is only for the sa group.

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 2

If the people are working with the database, they may be doing something. I would notify users in advance, then see who is connecting and call them.

Regards,Yelena Varsha

SQLBill SSC Guru Points: 51440 More actions · Answer 3

More things to consider:

If you are using Enterprise Manager and open Query Analyzer to run the ALTER DATABASE command - that is two connections. You must close EM and run the query with only QA open.

If you have any jobs running, those are connections. Consider stopping the SQLServerAgent service.

-SQLBill

cmille19 SSCertifiable Points: 5970 More actions · Answer 4

Sometimes restricting access to dbo doesn't work considering some applications have dbo access to the database. In addition single user mode will almost never work if the application reconnects quickly, it will take the single user connection to the database. It also sometimes difficult to track down all of the applications that connect to a database and have the applications shutdown. So here's a technique that will always work:

1. Make a connection to your SQL Server

2. Run NET PAUSE MSSQL<$InstanceName> or use the Services control panel to pause. Pausing the SQL Server will keep all current connections but not allow any new connections to the entire SQL Server instance.

3. Kill any connections to the database you want to restore and since you paused the SQL service they won't be able to reconnect

4. Kick off your restore

5. Run NET CONTINUE MSSQL<$InstanceName>, you can do this before the restore completes

NET PAUSE effects the entire SQL instance, so you may not be able to use this technique on a shared SQL box with many applications, however you can usually do something like this on non-production boxes.

Lowell SSC Guru Points: 323518 More actions · Answer 5

assuming you know it is ok to kicke them out without ill effects, kill their connections with this procedure: put it in master;

usage is simply sp_kill dbname

typical results:

sp_kill PRODUCTION	NO Processes Exist to be killed on database PRODUCTION
sp_kill PRDOUCTION	No database exists with the name PRDOUCTION, Check the Spelling of the db. NO Processes Exist to be killed on database PRDOUCTION
sp_kill PRODUCTION	Spid Process Kill List For database: PRODUCTION 51 - DAISY 52 - DAISY 53 - DAISY 55 - DAISY 57 - DAISY 58 - DAISY 59 - DAISY 61 - DAISY
sp_kill PRODUCTION	Spid Process Kill List For database: PRODUCTION Server: Msg 6104, Level 16, State 1, Line 1 Cannot use KILL to kill your own process.

--enhanced 02/04/2005 to also list hostname

CREATE PROCEDURE sp_Kill

@DBNAME VARCHAR(30)

--Stored procedure to Delete SQL Process

AS

BEGIN

SET NOCOUNT ON

DECLARE @SPID INT

DECLARE @STR NVARCHAR(50)

DECLARE @HOSTNAME NVARCHAR(50)

DECLARE @DBID INT

CREATE TABLE #TMPLOG (

SPID INT,

ECID INT,

STATUS VARCHAR(50),

LOGINAME VARCHAR(255),

HOSTNAME VARCHAR(50),

BLK INT,

DBNAME VARCHAR(30),

CMD VARCHAR(100)

&nbsp

select @DBID=db_id(@DBNAME)

IF @DBID IS NULL

PRINT 'No database exists with the name ' + @DBNAME + ', Check the Spelling of the db.'

INSERT INTO #TMPLOG EXEC SP_WHO

IF @@ERROR <> 0 GOTO Error_Handle

DECLARE CURPROCESSID CURSOR FOR SELECT SPID FROM #TMPLOG

WHERE DBNAME LIKE @DBNAME

OPEN CURPROCESSID

FETCH NEXT FROM CURPROCESSID INTO @SPID

SELECT @HOSTNAME=HOSTNAME FROM #TMPLOG WHERE SPID=@SPID

IF @SPID IS NOT NULL

PRINT 'Spid Process Kill List For database: ' + @dbName

ELSE

PRINT 'NO Processes Exist to be killed on database ' + @dbName

WHILE @@FETCH_STATUS = 0

BEGIN

SET @STR = 'KILL ' + CONVERT(VARCHAR,@SPID)

EXEC SP_EXECUTESQL @STR

PRINT convert(varchar,@spid) + ' - ' + @HOSTNAME

IF @@ERROR <> 0 GOTO ERROR_HANDLE

FETCH NEXT FROM CURPROCESSID INTO @SPID

END

Error_Handle:

IF @@ERROR <> 0 PRINT 'Error killing process - ' + convert(varchar,@spid) + ' - ' + @HOSTNAME

drop table #tmpLog

SET NOCOUNT OFF

END

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Markus Veretin SSCommitted Points: 1565 More actions · Answer 6

The easiest way to remove user's connections is in EM right click on DB name >> all tasks >> detach database and press clear button to kill all connections. After that click Cancel and start restore.

jim Edwards-308192 Say Hey Kid Points: 680 More actions · Answer 7

Thanks everyone for your help!!! Greatly appreciated

jim