Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Kill all connection to database Expand / Collapse
Author
Message
Posted Monday, February 28, 2011 8:37 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, February 06, 2014 4:51 AM
Points: 711, Visits: 214
Comments posted to this topic are about the item Kill all connection to database
Post #1070958
Posted Tuesday, March 01, 2011 7:10 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 1:11 PM
Points: 242, Visits: 676
i often use something more simple
DECLARE @TARGET INT
WHILE 1=1
BEGIN
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 KILLCONS
END

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


--
Thiago Dantas
@DantHimself
Post #1071207
Posted Tuesday, March 01, 2011 7:37 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, February 06, 2014 4:51 AM
Points: 711, Visits: 214
You're right - this procedure would be much more simple if I used cursors as You did.
Post #1071243
Posted Tuesday, March 08, 2011 12:49 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 23, 2012 10:34 PM
Points: 18, Visits: 162
i use this:

ALTER DATABASE Northwind SET SINGLE_USER WITH ROLLBACK IMMEDIATE
EXEC sp_dboption 'Northwind', 'single user', 'FALSE'

Post #1074621
Posted Thursday, May 19, 2011 6:54 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 04, 2014 3:15 AM
Points: 14, Visits: 394
--Kill all users instantly

use master
go
alter database test set offline with rollback immediate
go
alter database test set online
go
Post #1111644
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse