Exclusive use of the database

[Savil]

Hi
Is it possible by use of a script to kick all users off a db and ensure complete and exclusive use of this db to enable the db to be renamed?

thanks

 

[MartinCroft]

Hi

you can put the database into single user mode
through enterprise manager one of the options on the relevant database properties tab, this calls sp_dboption which you can lookup in BOL ( so can be used in a script)

exec sp_dboption N'NorthWIND', N'single',TRUE


This however will only work if no users are in the database
you will need to use

kill [spid]

for the users in the database, this should be used with caution and its probably best if these uses are given the opportunity to leave quitely !!

 

[DBAWinnipeg]

Hey Savil

Another way to do it (which will rollback any user transactions automatically)

ALTER DATABASE dbName
SET SINGLE_USER WITH ROLLBACK IMMEDIATE


agree with mysticmart that if at all possible let the users leave on their own for the "cleanest" way.

 

[Savil]

I will give it a try, thanks

 

[gashandler]

Someone posted a set of procedures last year that kills all users and then sets it to single-use only.:

Corran007 (Programmer) Sep 22, 2003
sure. i have a script that kills all connections to the datbase. what i would do is kill the conns, then put it in single user mode, then detach, copy, then reatach.

i got this from somewhere


CREATE PROCEDURE uspKillAllProcesses
@pDbName varchar (100)=NULL, /*database where we will kill processes.
If NULL-we will attempt to kill processes in all DBs*/
@pUserName varchar (100)=NULL /*user in a GIVEN database or in all databases where such a user name exists,
whose processes we are going to kill. If NULL-kill all processes. */

/*Purpose: Kills all processes in a given database and/or belonging to a specified user.
If no parameters supplied it will attempt to kill all user processes on the server.
Server: all
Database: DBAservice
Created: Igor Raytsin,Yul Wasserman 2000-10-13
Last modified: Yul Wasserman 2002-02-08
*/
AS
SET NOCOUNT ON

DECLARE @p_id smallint
DECLARE @dbid smallint
DECLARE @dbname varchar(100)
DECLARE @exec_str varchar (255)
DECLARE @error_str varchar (255)

IF NOT EXISTS (SELECT * FROM master.dbo.sysdatabases where name=ltrim(rtrim(@pDbName)) or @pDbName is NULL)
BEGIN
Set @error_str='No database '+ltrim(rtrim(@pDbName)) +' found.'
Raiserror(@error_str, 16,1)
RETURN-1
END
Create Table ##DbUsers(dbid smallint,uid smallint)

If @pUserName is not null
BEGIN
--Search for a user in all databases or a given one
DECLARE curDbUsers CURSOR FOR
SELECT dbid,name FROM master.dbo.sysdatabases where name=ltrim(rtrim(@pDbName)) or @pDbName is NULL
OPEN curDbUsers
FETCH NEXT FROM curDbUsers INTO @dbid,@dbname
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @exec_str='Set quoted_identifier off
INSERT ##DbUsers SELECT '+cast(@dbid as char)+', uid FROM '+@dbname+'.dbo.sysusers
WHERE name="'+ltrim(rtrim(@pUserName))+'"'
EXEC (@exec_str)
FETCH NEXT FROM curDbUsers INTO @dbid,@dbname
END
CLOSE curDbUsers
DEALLOCATE curDbUsers
If not exists(Select * from ##DbUsers)
BEGIN
Set @error_str='No user '+ltrim(rtrim(@pUserName)) +' found.'
DROP TABLE ##DbUsers
Raiserror(@error_str, 16,1)
RETURN-1
END
END

ELSE --IF @pUserName is null
BEGIN
INSERT ##DbUsers SELECT ISNULL(db_id(ltrim(rtrim(@pDbName))),-911),-911
END

--select * from ##dbUsers

DECLARE curAllProc CURSOR FOR
SELECT spid,sp.dbid FROM master.dbo.sysprocesses sp
INNER JOIN ##DbUsers t ON (sp.dbid = t.dbid or t.dbid=-911) and (sp.uid=t.uid or t.uid=-911)
OPEN curAllProc
FETCH NEXT FROM curAllProc INTO @p_id, @dbid

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @exec_str = 'KILL '+ Convert(varchar,@p_id)+ ' checkpoint'
SELECT @error_str = 'Attempting to kill process '+Convert(varchar,@p_id)+' in database '+db_name(@dbid)
RAISERROR (@error_str,10,1)with log
EXEC (@exec_str)
FETCH NEXT FROM curAllProc INTO @p_id, @dbid
END
CLOSE curAllProc
DEALLOCATE curAllProc
DROP TABLE ##DbUsers
SET NOCOUNT OFF

that kills them all.

this can set them to skingle user mode

CREATE PROC uspSetSingleuser
/*******************
Production server: all
Production database: DBAservice
Purpose: sets a database in single user /dbo only mode,
can be used with restore operations
Developed: Yul Wasserman 01/23/02
*****************/
@pDatabase varchar (100), @pDboOnly bit=1, @pAttempts int =15
AS
Declare @i int --counter
Declare @MyError int
set @i=0
IF @pDboOnly =1
Begin
exec sp_dboption @pDatabase,'dbo use only','true'
End
exec dbaService.dbo.uspKillAllProcesses @pDatabase
exec sp_dboption @pDatabase,'Single user', 'true'
Set @MyError=@@Error
While (@MyError <>0 AND @i<@pAttempts)
Begin
exec dbaService.dbo.uspKillAllProcesses @pDatabase
exec sp_dboption @pDatabase,'Single user','true'
Set @MyError=@@Error
set @i=@i+1
End


you can just set it up to run together since both are stored procedures.