SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Rafal S. / Kill all connection to database / Latest Posts

RE: Kill all connection to database

Oliver Wootton — Thu, 19 May 2011 06:54:42 GMT

--Kill all users instantlyuse mastergoalter database test set offline with rollback immediategoalter database test set onlinego

RE: Kill all connection to database

rktn_odin — Tue, 08 Mar 2011 00:49:11 GMT

i use this:[code="sql"]ALTER DATABASE Northwind SET SINGLE_USER WITH ROLLBACK IMMEDIATEEXEC sp_dboption 'Northwind', 'single user', 'FALSE'[/code]

RE: Kill all connection to database

skra — Tue, 01 Mar 2011 07:37:26 GMT

You're right - this procedure would be much more simple if I used cursors as You did.

RE: Kill all connection to database

dant12 — Tue, 01 Mar 2011 07:10:17 GMT

i often use something more simple[code="sql"]DECLARE @TARGET INTWHILE 1=1BEGIN DECLARE KILLCONS CURSOR LOCAL FAST_FORWARD FOR SELECT SPID FROM MASTER..SYSPROCESSES WHERE DBID = DB_ID('DB NAME') AND SPID >=50 OPEN KILLCONS FETCH NEXT FROM KILLCONS INTO @TARGET WHILE @@FETCH_STATUS = 0 BEGIN EXEC ('KILL '+@TARGET) FETCH NEXT FROM KILLCONS INTO @TARGET END CLOSE KILLCONS DEALLOCATE KILLCONSEND[/code]the infinite while is to take care of pesky applications that keep reconnecting, so you just manually break the loop when you feel like it

Kill all connection to database

skra — Mon, 28 Feb 2011 20:37:42 GMT

Comments posted to this topic are about the item [B]Kill all connection to database[/B]