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 12»»

Kill Active connections Expand / Collapse
Author
Message
Posted Sunday, October 4, 2009 10:32 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, June 23, 2014 6:03 AM
Points: 1,154, Visits: 866
Comments posted to this topic are about the item Kill Active connections
Post #797567
Posted Wednesday, October 21, 2009 5:20 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, February 25, 2014 1:05 AM
Points: 122, Visits: 167
Hi

This is quicker:
alter database [db_name] set single_user with rollback immediate
go

Post #806384
Posted Wednesday, October 21, 2009 7:09 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, June 21, 2014 6:47 AM
Points: 41, Visits: 70
the original script might kill connections that are active, what about new incoming connections, they will still be created.

With the set single_user also it is not possible to achieve the desired results, for e.g. in a farm environment where you have 10 web front ends connecting to a single sql cluster instance.
Post #806461
Posted Wednesday, October 21, 2009 7:19 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 26, 2013 8:14 AM
Points: 134, Visits: 165
Thanks for the script. It's useful for me.
Post #806470
Posted Wednesday, October 21, 2009 8:11 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 14, 2013 9:27 AM
Points: 266, Visits: 161
Here is a variation of the kill script that can be used on any database whether or not it is the current database. To use, set the @dbname variable to the name of the database where the connections are to be killed.

declare @dbname sysname, @spid int, @cnt int, @sql varchar(2048)

set @dbname = 'targetDB'

select @spid = min(spid), @cnt = count(*)
from master.dbo.sysprocesses
where dbid = db_id(@dbname) and spid != @@spid

print @dbname + ' connection process(es) to kill: ' + rtrim(@cnt)

while @spid is not null
begin
print 'kill connection process ' + rtrim(@spid)
set @sql = 'kill ' + rtrim(@spid)
exec(@sql)
select @spid = min(spid), @cnt = count(*)
from master.dbo.sysprocesses
where dbid = db_id(@dbname)
and spid != @@spid
end

print 'done'

Post #806523
Posted Wednesday, October 21, 2009 9:36 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 30, 2014 9:17 AM
Points: 4, Visits: 193
This script will save me a lot of time when restoring test databases. It's simply and replaces a process I was doing manually.
Thanks.
Post #806607
Posted Wednesday, October 21, 2009 3:47 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:22 AM
Points: 3, Visits: 144
I agree, I think the ALTER DATABASE <databaseName> SET SINGLE_USER WITH ROLLBACK IMMEDIATE is more elegant and precludes the risk of active connections occurring while the cursor is killing the ones it knew about at the time the cursor was opened.


Post #806853
Posted Wednesday, October 21, 2009 4:03 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 11:09 PM
Points: 3,108, Visits: 11,502
Setting the database to single user do not prevent someone from making a connection. It just kills the current connections, and prevent users from making more than one new connection.

If all you need to do is get everyone out of the database to do a restore, this will do it:
use master
alter database [db_name] set offline with rollback immediate








Post #806857
Posted Wednesday, October 21, 2009 5:33 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 30, 2014 9:17 AM
Points: 4, Visits: 193
use master
alter database [db_name] set offline with rollback immediate

I will try this next time I replace a test DB with a copy of production. Makes sense.

Does a restore automatically set the database online? I'll test when I try it.
Post #806887
Posted Wednesday, October 21, 2009 5:41 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, June 21, 2014 6:47 AM
Points: 41, Visits: 70
After setting a db to offline it wont be available for a restore, however, you put all statements in a single transaction

--Step1
alter database <dbname>
set offline
with rollback immediate -- this is the part that kills the connections

--Step2
alter database <dbname> set online

restore database <dbname> <restore parameters>

[b]Credit: This was actually given to me by an on call DBA. Nice alternative instead of having to shutdown IIS on all my web servers to do a db restore./b]
Post #806890
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse