SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


(new) Kill ALL Connections To a SQL Database


(new) Kill ALL Connections To a SQL Database

Author
Message
Richard Lu-422582
Richard Lu-422582
SSC-Enthusiastic
SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)

Group: General Forum Members
Points: 167 Visits: 70
Comments posted to this topic are about the item (new) Kill ALL Connections To a SQL Database
matthew_bluhm
matthew_bluhm
SSC Rookie
SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)

Group: General Forum Members
Points: 49 Visits: 73
If you are attempting to get the db into single user mode, you can use.


ALTER DATABASE db_name
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
mannaggia
mannaggia
Old Hand
Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)

Group: General Forum Members
Points: 312 Visits: 299
I just do this:

ALTER DATABASE dbname
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

ALTER DATABASE dbname
SET MULTI_USER
GO



Any advantage or disadvantage over the script?
TravisDBA
TravisDBA
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4508 Visits: 3069
mannaggia (2/15/2011)
I just do this:

ALTER DATABASE dbname
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

ALTER DATABASE dbname
SET MULTI_USER
GO



Any advantage or disadvantage over the script?


This works easier for me. The only reason to kill all the spids usually anyway is to put the database into another mode besides multi_user. :-D

"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
mannaggia
mannaggia
Old Hand
Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)

Group: General Forum Members
Points: 312 Visits: 299
TravisDBA (2/15/2011)
This works easier for me. The only reason to kill all the spids usually anyway is to put the database into another mode besides multi_user. :-D

I use it when I need to restore a database and there are open connections to the database I want to restore. (This is usually during development when I need to restore a copy of a production database onto a staging or development server.)
TravisDBA
TravisDBA
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4508 Visits: 3069
mannaggia (2/15/2011)
TravisDBA (2/15/2011)
This works easier for me. The only reason to kill all the spids usually anyway is to put the database into another mode besides multi_user. :-D

I use it when I need to restore a database and there are open connections to the database I want to restore. (This is usually during development when I need to restore a copy of a production database onto a staging or development server.)


Yep, me too but another SPID can sneak in there before you start the restore for some reason, so I always just put the database in single user mode right after in the script where I do the ALTER DATABASE with ROLLBACK IMMEDIATE and right before the restore...:-D

"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
Eric M Russell
Eric M Russell
SSC-Forever
SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)

Group: General Forum Members
Points: 43355 Visits: 12036
mannaggia (2/15/2011)
I just do this:

ALTER DATABASE dbname
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

ALTER DATABASE dbname
SET MULTI_USER
GO



Any advantage or disadvantage over the script?

I guess it depends on the conext of the situation, but I'd generally reccomend RESTRICTED_USER (allowing any SYSADMIN to connect multiple times) over SINGLE_USER, which allows ANY user to grab the next connection exclusively.


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
Richard Lu-422582
Richard Lu-422582
SSC-Enthusiastic
SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)

Group: General Forum Members
Points: 167 Visits: 70
As good as "ALTER DATABASE SET SINGLE_USER WITH ROLLBACK IMMEDIATE" can be, in some situations it can't be successful. You may get the below errors if there are uncommitted distributed transactions access the database. My script is designed for overcoming these...saving a trip to restart the SQL instance.

Msg 5061, Level 16, State 1, Line 1
ALTER DATABASE failed because a lock could not be placed on database 'Test'. Try again later.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.

The "ALTER Database Set " command is not a guarrantee, folks. Wish everyone would never have to use my Kill script :-)
Richard Lu-422582
Richard Lu-422582
SSC-Enthusiastic
SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)

Group: General Forum Members
Points: 167 Visits: 70
"Alter Database" usually works. However, it may fail if there are un-committed distributed transactions. In this case the SPID is not showing under the database, and can't be terminated by "Alter Database"

I usually would run the "Kill" script I posted here to "clean up" ALL, explicit and implicit, connections. Then Run the "Alter Database Set Single_User..." to claim the exclusive connection right to the database in order to do my work.

I have seen DBAs got stuck in this situation, and ended up going for the last resort: restart SQL.

Hope the above has explained.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search