(new) Kill ALL Connections To a SQL Database

Question

Post reply

(new) Kill ALL Connections To a SQL Database

Richard Lu-422582

SSC-Addicted

Points: 417
More actions
February 14, 2011 at 9:07 pm

#234367

Comments posted to this topic are about the item (new) Kill ALL Connections To a SQL Database

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

matthew_bluhm SSC Journeyman Points: 95 More actions · Answer 1

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 SSC Eights! Points: 904 More actions · Answer 2

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 SSCoach Points: 15780 More actions · Answer 3

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. ...:-D"

mannaggia SSC Eights! Points: 904 More actions · Answer 4

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

TravisDBA SSCoach Points: 15780 More actions · Answer 5

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...:-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 SSC Guru Points: 125562 More actions · Answer 6

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.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Richard Lu-422582 SSC-Addicted Points: 417 More actions · Answer 7

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 SSC-Addicted Points: 417 More actions · Answer 8

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

colt Old Hand Points: 317 More actions · Answer 9

Sorry I'm late to the party, but,

sys.syslockinfo does not exist in SQL SERVER 2000.

Use master.dbo.syslockinfo in place of sys.syslockinfo to get the script to work in SQL 2000