Dumping all users off a database
-
I need to detatch a database but it SQL wont let me do it as there are users using the database. Is there a stored procedure which will dump everyone out of it in one go, leaving it clear for me to work on? Thank You
-
Hello James
Check out "DISCONNECT" in Books Online help.
Thanks and have a great day!!!
Lucky -
or... if you want to be careful you can just go to the sql service manager (icon located usually at the bottom
right in the system tray) or find it under the sql server program group...
any how just look for 'pause'.
basically this will just not allow any new connections.
each time a person logs off, or a transaction completes
sql server will not allow any new work. all existing
work/connections is maintained till the transaction is
completed.
it 'might' take some time, but eventually all users
will not be able reconnect.
_________________________
-
I haven't looked in the scripts for a while, but I think what your looking for is a kill scripts. Here is one that takes a parameter of database name and will kill all connections to that database. I usually run this first and in the same query window set my db_option using sp_dboption 'single user', true; sp_dboption 'dbo use only','true';
Tom
CREATE PROCEDURE OTC_DBA_KILLUSERS(@vcDbName VARCHAR(30))
AS
SET NOCOUNT ON
/***** Declaritive section *****/
DECLARE @vcDbId VARCHAR(3)
DECLARE @vcSpid VARCHAR(4)
DECLARE @sSpid INT
DECLARE @vcKillCmd VARCHAR(12)
set @vcSpid = ' '
/***** This will make sure that the database name is not master, model, msdb, or tempdb *****/
IF @vcDbName = 'MASTER' OR
@vcDbName = 'MODEL' OR
@vcDbname = 'MSDB' OR
@vcDbName = 'TEMPDB'
BEGIN
PRINT 'CANNOT KILL USERS FROM '+UPPER(@vcDbName)+' DATABASE'
RETURN
END
ELSE
/***** This veryfies that the database exists in the sysdatabases table *****/
IF @vcDbName not in (SELECT NAME FROM SYSDATABASES
WHERE NAME = @vcDbName)
BEGIN
PRINT 'DATABASE '+UPPER(@vcDbName) +' DOES NOT EXIST. PLEASE CHECK SPELLING.'
RETURN
END
ELSE
/***** This is the beginning of the script *****/
BEGIN
SELECT @vcDbId = dbid FROM SYSDATABASES
WHERE NAME = @vcDbName
WHILE @vcSpid IS NOT NULL
BEGIN
SELECT @vcSpid = min(cast(spid as varchar(4)))
FROM SYSPROCESSES
WHERE dbid = @vcDbid
AND spid > cast(@vcSpid as int)
if @vcSpid is null
begin
Print 'There are no current connections in -- '+@vcDbname
end
IF @vcSpid IS NOT NULL
BEGIN
EXEC ('KILL '+@vcSpid)
PRINT 'KILLING CONNECTIONS IN THE '+UPPER(@vcDbname)+' DATABASE: SPID # '+@vcSpid
END
END
END
GO
-
If you stop the sql service Named MSSQLSERVER
it will kick everyone off SQL Server.
Then you can restart the service.
Might want to send an email first.
-
here's another version of a cursor to kill users off of a database; i put this in the master database;
syntax is sp_kill databasename and it will list the hostnames of the connections it kills:
--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)
CREATE TABLE #TMPLOG (
SPID INT,
ECID INT,
STATUS VARCHAR(50),
LOGINAME VARCHAR(255),
HOSTNAME VARCHAR(50),
BLK INT,
DBNAME VARCHAR(30),
CMD VARCHAR(100)
 
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! -
much easier:-
alter database xxxx set restricted_user with rollback immediate
or
single_user
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply