March 29, 2010 at 5:12 am
Hi,
I need to execute a procedure that will drop some logins from SQL Server 2005.
I need to be chure that this logins are not connected to SQL Server, so i need to CUT all the connections from the server other then my connection.
how can i accomplish this task?
Thank you,
Pedro Ribeiro
March 29, 2010 at 5:38 am
identify all process ID's from sysprocess table of the particular user.
Kill all processes one by one.. write some logic with loops.
and then drop the login.
March 29, 2010 at 5:42 am
Do the dreaded cursor, and use dynamic sql to throw in the spids, on the "Kill" command.
It's usually efficient and effective, but if any threads are rolling back, nothing you do will immediately disconnect the users.
EDIT: Um, I should say nothing you do short of stopping the services will immediately disconnect the users.
March 29, 2010 at 5:55 am
u can also run the following command to disconnect all users
alter database <dbname> set single_user with rollback immediate
March 29, 2010 at 6:08 am
Oh, well, I suppose *that* works too.
@=)
March 29, 2010 at 6:15 am
u can think of the method which will have less impact and fast solution. 😎
March 29, 2010 at 8:08 am
create procedure RemoveLigacoes
AS
BEGIN
DECLARE @DatabaseName nvarchar(100)
DECLARE @SPId int
DECLARE my_DBs CURSOR FAST_FORWARD FOR
select [name] from master.sys.databases
WHERE [NAME] NOT IN ('MASTER','MODEL','TEMPDB','MSDB','ADVENTUREWORKS')
OPEN my_DBs
FETCH NEXT FROM my_DBs INTO @databasename
WHILE @@FETCH_STATUS = 0
Begin
-------------Second cursor------------------
DECLARE my_Spid CURSOR FAST_FORWARD FOR
SELECT SPId FROM MASTER..SysProcesses
WHERE DBId = DB_ID(@DatabaseName) AND SPId <> @@SPId
OPEN my_Spid
FETCH NEXT FROM my_Spid INTO @SPId
WHILE @@FETCH_STATUS = 0
BEGIN
exec ('KILL '+@SPId)
FETCH NEXT FROM my_cursor INTO @SPId
END
CLOSE my_Spid
DEALLOCATE my_Spid
FETCH NEXT FROM my_DBs INTO @databasename
END
CLOSE my_DBs
DEALLOCATE my_DBs
END
March 29, 2010 at 8:49 am
And apparently we never caught the "all connections on the server" part of the OP.
DOH!
March 29, 2010 at 8:54 am
Didn't undestood your answer...
March 29, 2010 at 9:28 am
I'm receiving this error when executed the stored procedure that i posted:
Msg 217, Level 16, State 1, Procedure RemoveLigacoes, Line 48
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
What does this means?
Thank you
March 29, 2010 at 9:33 am
March 29, 2010 at 10:38 am
river1 (3/29/2010)
I'm receiving this error when executed the stored procedure that i posted:Msg 217, Level 16, State 1, Procedure RemoveLigacoes, Line 48
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
What does this means?
It means SQL Server looped through your cursors too many times.
You might have to get rid of your first cursor and use a sp_forEachDB proc. This is an undocumented stored proc, so it's not in BOL. Google it and you should come up with links that tells you how to do it.
Or maybe someone here who's had more experience with it can tell you how to work it into your cursor.
March 29, 2010 at 10:50 am
ok how about this:
first that other cursor has a logic hole; don't loop thru databaszes, loop thru connections.
i adapted my sp_kill function, which is database specific, to do the whole server;
results look like this:
Spid Process Kill List For server: DBSQL2K5
...
67 - DBSQL2K5 on master
68 - DBSQL2K5 on master
69 - DBSQL2K5 on OCD_HDS_03232010
70 - DBSQL2K5 on master
Cannot kill your own SPID, skipping 71 - DBSQL2K5
72 - DBSQL2K5 on LAHFA9040
...
137 - DBSQL2K5 on LAHFA03232010
145 - DBSQL2K5 on AZ_900_TEST
155 - DBSQL2K5 on master
and here's my proc:
be freaking careful:
run this on production and you've disconnected everyone except yourself!
because of that i comented out the KILL command...un comment it yourself so you can wreak havok.
CREATE PROCEDURE sp_disconnect
--Stored procedure to Delete all SQL Processes
AS
BEGIN
SET NOCOUNT ON
DECLARE @SPID INT
DECLARE @DBNAME VARCHAR(50)
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) ,
RID INT,
)
INSERT INTO #TMPLOG EXEC SP_WHO
--do not even try to kill spids that are SQL server itself.
DELETE FROM #TMPLOG WHERE SPID < 50
IF @@ERROR <> 0 GOTO Error_Handle
DECLARE CURPROCESSID CURSOR FOR SELECT SPID,dbname FROM #TMPLOG
OPEN CURPROCESSID
FETCH NEXT FROM CURPROCESSID INTO @SPID,@DBNAME
SELECT @HOSTNAME=HOSTNAME FROM #TMPLOG WHERE SPID=@SPID
IF @SPID IS NOT NULL
PRINT 'Spid Process Kill List For server: ' + @@SERVERNAME
ELSE
PRINT 'NO Processes Exist to be killed on database ' + ISNULL(@dbName,'-')
WHILE @@FETCH_STATUS = 0
BEGIN
IF @SPID = @@SPID
BEGIN
PRINT 'Cannot kill your own SPID, skipping ' + convert(varchar,@spid) + ' - ' + @HOSTNAME
END
ELSE
BEGIN
SET @STR = 'KILL ' + CONVERT(VARCHAR,@SPID)
--EXEC SP_EXECUTESQL @STR
PRINT convert(varchar,@spid) + ' - ' + @HOSTNAME + ' on ' + ISNULL(@dbName,'-')
END
IF @@ERROR <> 0 GOTO ERROR_HANDLE
FETCH NEXT FROM CURPROCESSID INTO @SPID,@DBNAME
END
Error_Handle:
IF @@ERROR <> 0 PRINT 'Error killing process - ' + convert(varchar,@spid) + ' - ' + @HOSTNAME + ' on ' + ISNULL(@dbName,'-')
drop table #tmpLog
SET NOCOUNT OFF
END
Lowell
March 29, 2010 at 11:28 am
The one thing to note about killing connections this way is that it does NOT prevent those connections from reconnecting after they've been disconnected. The best way to do that is set all the databases to Single User.
March 30, 2010 at 1:26 am
The procedure that Lowel gave me is very usefull but i think that you are right , it's better to put all the databases in to single user mode and then (at the end) set the databases to multi user again.
Thank you.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply