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

(new) Kill ALL Connections To a SQL Database Expand / Collapse
Author
Message
Posted Monday, February 14, 2011 9:07 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, June 10, 2011 2:04 PM
Points: 65, Visits: 67
Comments posted to this topic are about the item (new) Kill ALL Connections To a SQL Database
Post #1063979
Posted Tuesday, February 15, 2011 2:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 25, 2014 8:02 AM
Points: 1, Visits: 53
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
Post #1064109
Posted Tuesday, February 15, 2011 6:54 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, September 6, 2014 1:54 PM
Points: 25, Visits: 249
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?
Post #1064235
Posted Tuesday, February 15, 2011 8:09 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 2:03 PM
Points: 1,335, Visits: 3,069
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.


"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ..."
Post #1064318
Posted Tuesday, February 15, 2011 8:20 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, September 6, 2014 1:54 PM
Points: 25, Visits: 249
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.

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.)
Post #1064326
Posted Tuesday, February 15, 2011 11:15 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 2:03 PM
Points: 1,335, Visits: 3,069
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.

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...


"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ..."
Post #1064453
Posted Wednesday, February 16, 2011 8:36 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 1:39 PM
Points: 1,660, Visits: 4,748
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.
Post #1065047
Posted Monday, April 11, 2011 11:29 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, June 10, 2011 2:04 PM
Points: 65, Visits: 67
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
Post #1091617
Posted Monday, April 11, 2011 11:45 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, June 10, 2011 2:04 PM
Points: 65, Visits: 67
"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.
Post #1091628
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse