(new) Kill ALL Connections To a SQL Database

  • Richard Lu-422582

    SSC-Addicted

    Points: 417

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

  • matthew_bluhm

    SSC Journeyman

    Points: 95

    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

    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

    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

    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

    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: 125094

    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

    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

    "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

    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

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

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