Database In Use - Removing Users Quickly - How?
-
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
-
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.
-
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
-
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
-
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.
-
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)
 
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! -
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.
-
Thanks everyone for your help!!! Greatly appreciatedjim
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply