SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Richard Lu / (new) Kill ALL Connections To a SQL Database / Latest Posts

RE: (new) Kill ALL Connections To a SQL Database

Richard Lu-422582 — Mon, 11 Apr 2011 11:45:20 GMT

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

RE: (new) Kill ALL Connections To a SQL Database

Richard Lu-422582 — Mon, 11 Apr 2011 11:29:51 GMT

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 1ALTER DATABASE failed because a lock could not be placed on database 'Test'. Try again later.Msg 5069, Level 16, State 1, Line 1ALTER DATABASE statement failed.The "ALTER Database Set " command is not a guarrantee, folks. Wish everyone would never have to use my Kill script :-)

RE: (new) Kill ALL Connections To a SQL Database

Eric M Russell — Wed, 16 Feb 2011 08:36:17 GMT

[quote][b]mannaggia (2/15/2011)[/b][hr]I just do this:[code="sql"]ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATEGOALTER DATABASE dbname SET MULTI_USERGO[/code]Any advantage or disadvantage over the script?[/quote]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.

RE: (new) Kill ALL Connections To a SQL Database

TravisDBA — Tue, 15 Feb 2011 11:15:35 GMT

[quote][b]mannaggia (2/15/2011)[/b][hr][quote][b]TravisDBA (2/15/2011)[/b]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[/quote]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.)[/quote]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

RE: (new) Kill ALL Connections To a SQL Database

mannaggia — Tue, 15 Feb 2011 08:20:57 GMT

[quote][b]TravisDBA (2/15/2011)[/b]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[/quote]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.)

RE: (new) Kill ALL Connections To a SQL Database

TravisDBA — Tue, 15 Feb 2011 08:09:05 GMT

[quote][b]mannaggia (2/15/2011)[/b][hr]I just do this:[code="sql"]ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATEGOALTER DATABASE dbname SET MULTI_USERGO[/code]Any advantage or disadvantage over the script?[/quote]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

RE: (new) Kill ALL Connections To a SQL Database

mannaggia — Tue, 15 Feb 2011 06:54:34 GMT

I just do this:[code="sql"]ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATEGOALTER DATABASE dbname SET MULTI_USERGO[/code]Any advantage or disadvantage over the script?

RE: (new) Kill ALL Connections To a SQL Database

matthew_bluhm — Tue, 15 Feb 2011 02:37:46 GMT

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

(new) Kill ALL Connections To a SQL Database

Richard Lu-422582 — Mon, 14 Feb 2011 21:07:46 GMT

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