none
how can i kill all connections of users to my sql server database ?

    Question

  • hi

     

    i have a small question here :

    is there a way to kill all connection of users to my sql server database

    in order to restore it ?

     

    i've tried a stored procedure using the proc ( kill ) :

    but this proc has not succeeded to kill the connection of my sqlConnection

    object , what should i do ? and can the SMO help me ?

     

    please answer

     

    regards.

     

    Saturday, November 04, 2006 12:41 PM

Answers

All replies

  •  

    I've used sp_who to get the list of connection spids, and then kill to kill them, comparing the spids to @@SPID to make sure I kill my own connection last.

    If you want to handle this via a T-SQL query, your best bet is to ask the question on the MSDN Forum for Transact-SQL: http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=85&SiteID=1

    If you want to handle this via SMO, your best bet is to ask the question on the MSDN Forum for SMO at: http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=88&SiteID=1

    Friday, November 10, 2006 2:30 AM
  • The graphical and perhaps easy way to do this would probably be to "Detach" the database temporarily and then reconnect to kill all active connections. You can do this by Right Clicking your database in SMSS and selecting detach. In the window that pops up, you could select drop connections and select OK to kill all connections...
    Friday, November 10, 2006 3:29 AM
  • Hi

     

    first thank you ..

     

    mr.David Sceppa - Microsoft  the tow links you've put here are the same !!

     

    Can me understand why ? And where are the answers exactly ?

     

    regards .

    Friday, November 10, 2006 11:56 AM
  •  

    I corrected the link for the SMO Forum.

    Your best bet would be to search those forums and post your question there if you don't find an answer.

    Monday, November 13, 2006 6:59 PM
  • Hi   David,
    To kill all the connected database users follow the below steps.
    Create a Store procdure with the following steps.

    1) Get the processes attached to that database:
      SELECT spid  FROM master..sysprocesses  WHERE dbid = DB_ID(@DatabaseName)
      and AND spid != @@SPID

    Note :  @@SPID  is your connected instance Service Process id "SPID"

    2) Perform Kill operation in a loop or use cursor
      EXEC('KILL '+RTRIM(@spid) )

    STORE PROCEDURE TO REMOVE ALL Connected Database users


      CREATE PROCEDURE dbo.clearDBUsers
        @dbName SYSNAME
    AS
    BEGIN
        SET NOCOUNT ON
     
        DECLARE @spid INT,
            @cnt INT,
            @sql VARCHAR(255)
     
        SELECT @spid = MIN(spid), @cnt = COUNT(*)
            FROM master..sysprocesses
            WHERE dbid = DB_ID(@dbname)
            AND spid != @@SPID
     
        PRINT 'Starting to KILL '+RTRIM(@cnt)+' processes.'
        
        WHILE @spid IS NOT NULL
        BEGIN
            PRINT 'About to KILL '+RTRIM(@spid)  
            SET @sql = 'KILL '+RTRIM(@spid)
            EXEC(@sql)  
            SELECT @spid = MIN(spid), @cnt = COUNT(*)
                FROM master..sysprocesses
                WHERE dbid = DB_ID(@dbname)
                AND spid != @@SPID  
            PRINT RTRIM(@cnt)+' processes remain.'
        END
    END
    GO

    Note :To get path of database  explore sysdatabases in masters database
    SELECT * FROM master..sysdatabases

    Thx
    Amar





    Thursday, June 21, 2007 7:37 AM
  •  

    I think what you would like to accomplish would most easily be done by executing the following tsql:

     

    ALTER DATABASE [DATABASE_NAME]

    SET SINGLE_USER

    WITH ROLLBACK IMMEDIATE

    • Proposed as answer by gao.seng Tuesday, January 10, 2012 5:00 PM
    Wednesday, February 27, 2008 4:50 PM
  • ALTER DATABASE will fail if the database is already in SINGLE_USER mode and another user is already connected.

    This happens to me often when developing with a C# database project with the 'set to single user mode' option.

    It's quite difficult to get out of this mode as Visual Studio and sometimes SQL Server Management Studio must both be closed to change back to Multi_User mode.

     

    Or TSQL must be used to terminate all other connections.

     


    • Edited by DamianAngel Monday, September 12, 2011 3:28 PM
    Monday, September 12, 2011 3:27 PM
  • Thank u
    Wednesday, September 28, 2011 1:28 PM
  • The best way to do this job is using the following commands:

    GO

    ALTER DATABASE <<your_db_name>>

    SET OFFLINE WITH ROLLBACK IMMEDIATE

    ALTER DATABASE <<your_db_name>>

    SET ONLINE

    GO


    Sams
    • Proposed as answer by JasonWilczak Thursday, February 23, 2012 1:44 PM
    Friday, November 18, 2011 5:12 PM
  • Use the following code snippet to disconnect all connections to a DB:
    ALTER DATABASE <<your_db_name>>
    SET OFFLINE WITH ROLLBACK IMMEDIATE
    ALTER DATABASE <<your_db_name>>
    SET ONLINE
    GO

    Sams
    • Proposed as answer by JasonWilczak Thursday, February 23, 2012 1:44 PM
    Friday, November 18, 2011 5:13 PM
  • this was easiest and quickest for me.  thanx!
    Wednesday, January 25, 2012 2:01 PM
  • Excellent solution, JasonWilczak! Worked like a charm, took ~40 secs.
    Wednesday, May 02, 2012 8:00 PM
  • Thanks, 

    This one helped.

    For the following to come - just to let you know this one worked

     and i had no need to change from multi_user to single_user mode.

    Thursday, May 31, 2012 7:31 PM
  • You might want to set to restricted_user in the event you have a busy database and you loose your connection to the database (assuming you're sysadmin/dbowner).
    Thursday, September 06, 2012 9:54 PM