Kicking out users in 2005 db? 2

[Ovatvvon]

If we needed to kick out all the users connected to a DB in SQL 2000, we could simply go to the "database detach" window and there was an option to kick out all the users. (Then we could just cancel the detach).

In SQL2005, it doesn't look like this option is stil available, and setting to Single-User mode doesn't seem to work either.

Does anyone know of a quick / simple way to kick out all users of any particular database so that actions can be performed against the database (i.e. such as a restore)?



-Ovatvvon :-Q

 

[mrdenny]

Select a list of the spids that are using the database from the master.dbo.sysprocesses view and kill those spids. Then use an alter database command to put the database into single user mode so that they can't get into the database.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[ptheriault]

I use this script.

DECLARE @strSQL varchar(255)
PRINT 'Killing Users'
PRINT '-----------------'

CREATE table #tmpUsers(
spid int,
eid int,
status varchar(30),
loginname varchar(50),
hostname varchar(50),
blk int,
dbname varchar(50),
cmd varchar(30),
requestid int)

INSERT INTO #tmpUsers EXEC SP_WHO


DECLARE LoginCursor CURSOR
READ_ONLY
FOR SELECT spid, dbname FROM #tmpUsers WHERE dbname = 'YOUR DB'

DECLARE @spid varchar(10)
DECLARE @dbname2 varchar(40)
OPEN LoginCursor

FETCH NEXT FROM LoginCursor INTO @spid, @dbname2
WHILE (@@fetch_status <> -1)
BEGIN
        IF (@@fetch_status <> -2)
        BEGIN
        PRINT 'Killing ' + @spid
        SET @strSQL = 'KILL ' + @spid
        EXEC (@strSQL)
        END
        FETCH NEXT FROM LoginCursor INTO  @spid, @dbname2
END

CLOSE LoginCursor
DEALLOCATE LoginCursor

DROP table #tmpUsers


GO



-- SP_WHO2 INsert
CREATE table #tmpUsers(
spid int,
STATUS varchar(50),
loginname varchar(50),
hostname varchar(50),
blk varchar(10),
dbname varchar(50),
cmd varchar(30),
CPUTIME varchar(255),
DISKIO varchar(255),
LASTBATCH Varchar(30),
ProgramName Varchar(255),
SPID2 int,
requestid int)

INSERT INTO #tmpUsers EXEC SP_WHO2
SELECT * FROM #tmpUsers
DROP TABLE #tmpUsers

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[ptheriault]

you don't need the SP_WHO2 stuff at the bottom. I was going to re-write it using sp_who2 but I haven't gotten around to it yet.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[Ovatvvon]

To kill the spids, I tried writing up the following script:

use master
go

DECLARE @strDatabaseName varchar(100)
DECLARE @spid int

SET @strDatabaseName = 'EnterDatabaseNameHere'

WHILE exists(select spid from dbo.sysprocesses where dbid = (select dbid from dbo.sysdatabases where name = @strDatabaseName))
BEGIN
	select TOP 1 @spid=spid from dbo.sysprocesses where dbid = (select dbid from dbo.sysdatabases where name = @strDatabaseName)
	kill @spid
END

but receive the following error:

Msg 102, Level 15, State 1, Line 10
Incorrect syntax near '@spid'.


Am I able to use a variable for the kill statement? If not, how can I kill those active spids dynamically?


-Ovatvvon :-Q

 

[Ovatvvon]

I created the SP, which works good. I just want to check on one last thing though. Another DBA in our group said that going out and searching for each individual SPID and killing it sometimes doesn't rollback correctly, etc. (I don't see why it would matter anyway if you're going to restore over the database, but...)

He said he's always used this, and he's never had a problem with it.

USE master;
GO
ALTER DATABASE AdventureWorks
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE AdventureWorks
SET MULTI_USER;
GO

Does anyone have any input on which method would be better (i.e. quicker, more seemless, etc)?


-Ovatvvon :-Q

 

[ptheriault]

The problem with setting the database in sinle user is that your spid might not be the one that connects first. So you would have have to kill the connected spid anyway. I've always used the script that I provided and I've never had any problems.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[mrdenny]

Agreed, I've never had problems killing spids and rollbacks.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Ovatvvon]

I didn't think so. I know we've run into a couple situations here when manually killing spid's, where the spid gets stuck in a rollback state, and says 0% complete, 0% left to go, etc. (very rarely, but it has happened). The only way we've found to recover from that is recycling SQL Server, which most of the time ends up crashing the box and we have to reboot the entire box as well. I'm not sure that either method listed above would make a difference in that particular situation, but that's the only thing I can think of that he (the other DBA) is referring to.

I think this will work though, and some of the other DBA's here think it is fine as well. (4 against 2...we win! )

Thanks all for your help!


-Ovatvvon :-Q

 

[mrdenny]

The reason that the situation you described happens is because the client machine doesn't respond correctly to the SQL Server telling it that it's been killed.

Basically what happens when you kill a session is this.

1. You issue the Kill command.
2. SQL Server stops processing the command.
3. SQL Server rolls back any open transactions.
4. SQL Server tells the client software that the session has been killed.
5. The client sends back an ACK.
6. The SQL Server closes the connection.

The problem comes up when the client software (ODBC drivers) don't respond correctly or don't respond at all in step 5. This causes the SQL Server to sometimes hang on the spid. Typically this is caused by the client software not being there any more. I've seen this happen if the client machine blue screens and isn't rebooted. This leaves the socket to the SQL Server open, but the software won't respond with the ACK.

It can happen in other circumstances, but it doesn't happen often.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Ovatvvon]

I don't think we've ever had a client machine/application server go down at the same time, at least not that I'm aware of. That is interesting on how that happens, however. Basically though, there is no way to prevent it; is that correct? (In which case, all the more evidence that this method to "kick out the users" would be fine)

-Ovatvvon :-Q

 

[mrdenny]

Correct there is no way to prevent it.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Ovatvvon]

Thanks for all your help!

-Ovatvvon :-Q

 

[mrdenny]

no problem.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com