How to close existing connections to a DB

Question

I want to close the existing connections to an MS SQL Server so that I can do a restore on that database programatically.

Possible duplicate: [How do you kill all current connections to a SQL Server 2005 database?](http://stackoverflow.com/questions/11620/how-do-you-kill-all-current-connections-to-a-sql-server-2005-database) — zimdanen, Apr 30 '12 at 17:47

score 105 · Accepted Answer · edited Mar 10 '17 at 14:59

105

This should disconnect everyone else, and leave you as the only user:

alter database YourDb set single_user with rollback immediate

Note: Don't forget

alter database YourDb set MULTI_USER

after you're done!

edited Mar 10 '17 at 14:59

Pradeep Kumar

4,065
2
33
40

answered Apr 30 '12 at 17:47

Andomar

232,371
49
380
404

20

Don't forget `ALTER DATABASE YourDb SET MULTI_USER` after you're done! – Tyler Forsythe Feb 08 '16 at 16:55
Apparantly, the first statement does not always work to drop all connections. At least not for me. I had to kill the process that was connected to the database first. – TT. Apr 13 '18 at 12:25
4

I am now past this problem. I read [here](https://dba.stackexchange.com/a/157921/65699) that using the following statement might help in my situation: `Alter database your_db_name set OFFLINE with rollback immediate` – TT. Apr 13 '18 at 12:34

leyla azari · Answer 2 · 2017-11-23T06:23:23.890

16

in restore wizard click "close existing connections to destination database"

in Detach Database wizard click "Drop connection" item.

edited Nov 23 '17 at 06:23

answered Feb 28 '15 at 08:45

leyla azari

913
11
20

1

This option isn't always available – Squazz Sep 01 '17 at 12:26
2

For those that are wondering why the option isn't always available, there's a workaround. If you go to the options tab BEFORE doing anything else and check the "Close existing connections" checkbox before doing any other operations in the restore dialog, it seems to work around the option being grayed out. – Grady Werner Dec 07 '18 at 17:54

score 6 · Answer 3 · answered Apr 30 '12 at 17:42

6

Found it here: http://awesomesql.wordpress.com/2010/02/08/script-to-drop-all-connections-to-a-database/

DECLARE @dbname NVARCHAR(128)
SET @dbname = 'DB name here'
 -- db to drop connections 
DECLARE @processid INT 
SELECT  @processid = MIN(spid)
FROM    master.dbo.sysprocesses
WHERE   dbid = DB_ID(@dbname) 
WHILE @processid IS NOT NULL 
    BEGIN 
        EXEC ('KILL ' + @processid) 
        SELECT  @processid = MIN(spid)
        FROM    master.dbo.sysprocesses
        WHERE   dbid = DB_ID(@dbname) 
    END

answered Apr 30 '12 at 17:42

Vinnie

3,889
1
26
29

thanks but this query took more than 2 mins so i just cancelled it – Haz Apr 30 '12 at 17:47
1

IMHO this is an unnecessarily complex and ineffective way to do it. @Andomar's answer accomplishes the same thing with much less effort and with better brute force control over in-flight transactions. It also doesn't capture users who have a different database reported in the *deprecated* sysprocesses view but are still holding locks on the current database. – Aaron Bertrand Apr 30 '12 at 17:52
3

Thank you Aaron for my weekly shaming. It kills the process, but certainly not elegantly. – Vinnie Apr 30 '12 at 18:03
3

It isn't about shame, it's about presenting valuable solutions not only to the OP but also to future readers. If there is a weakness in a solution I think it should be pointed out. – Aaron Bertrand Apr 30 '12 at 18:21
1

@Andomar's answear doesnt help it there is a connection already open! This script worked like a charm! – Jun 08 '15 at 07:54
Upon the first run the script failed with the error: *ID such-and-such is not an active process ID* but completed successfully upon the second. A condition to ignore non-existent `SPID`s might help. – Anton Shepelev Jul 01 '16 at 11:14
@Haz This command would be much faster if rewritten using a static cursor. – Anton Shepelev Jul 01 '16 at 13:29

score 3 · Answer 4 · answered Jan 07 '17 at 13:28

You can use Cursor like that:

USE master
GO

DECLARE @SQL AS VARCHAR(255)
DECLARE @SPID AS SMALLINT
DECLARE @Database AS VARCHAR(500)
SET @Database = 'AdventureWorks2016CTP3'

DECLARE Murderer CURSOR FOR
SELECT spid FROM sys.sysprocesses WHERE DB_NAME(dbid) = @Database

OPEN Murderer

FETCH NEXT FROM Murderer INTO @SPID
WHILE @@FETCH_STATUS = 0

    BEGIN
    SET @SQL = 'Kill ' + CAST(@SPID AS VARCHAR(10)) + ';'
    EXEC (@SQL)
    PRINT  ' Process ' + CAST(@SPID AS VARCHAR(10)) +' has been killed'
    FETCH NEXT FROM Murderer INTO @SPID
    END 

CLOSE Murderer
DEALLOCATE Murderer

I wrote about that in my blog here: http://www.pigeonsql.com/single-post/2016/12/13/Kill-all-connections-on-DB-by-Cursor

score 2 · Answer 5 · edited Jan 23 '14 at 11:14

Perfect solution provided by Stev.org: http://www.stev.org/post/2011/03/01/MS-SQL-Kill-connections-by-host.aspx

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[KillConnectionsHost]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[KillConnectionsHost]
GO


/****** Object:  StoredProcedure [dbo].[KillConnectionsHost]    Script Date: 10/26/2012 13:59:39 ******/

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[KillConnectionsHost] @hostname varchar(MAX)
AS
    DECLARE @spid int
    DECLARE @sql varchar(MAX)

    DECLARE cur CURSOR FOR
        SELECT spid FROM sys.sysprocesses P
            JOIN sys.sysdatabases D ON (D.dbid = P.dbid)
            JOIN sys.sysusers U ON (P.uid = U.uid)
            WHERE hostname = @hostname AND hostname != ''
            AND P.spid != @@SPID

    OPEN cur

    FETCH NEXT FROM cur
        INTO @spid

    WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT CONVERT(varchar, @spid)

        SET @sql = 'KILL ' + RTRIM(@spid)
        PRINT @sql
        EXEC(@sql)

        FETCH NEXT FROM cur
            INTO @spid
    END

    CLOSE cur
    DEALLOCATE cur
GO

its pretty long, but also the only solution worked for me! thx — curiousBoy, Dec 10 '17 at 01:40

score 2 · Answer 6 · answered Nov 01 '18 at 06:05

Short Answer:

You get "close existing connections to destination database" option only in "Databases context >> Restore Wizard" and NOT ON context of any particular database.

Long Answer:

Right Click on the Databases under your Server-Name as shown below:

and select the option: "Restore Database..." from it.

In the "Restore Database" wizard,

select one of your databases to restore
in the left vertical menu, click on "Options"

db1

Here you can find the checkbox saying, "close existing connections to destination database"

db3

Just check it, and you can proceed for the restore operation.

It automatically will resume all connections after completion of the Restore.

score 0 · Answer 7 · answered Dec 16 '20 at 09:18

0

In more recent versions of SQL Server Management studio, you can now right click on a database and 'Take Database Offline'. This gives you the option to Drop All Active Connections to the database.

answered Dec 16 '20 at 09:18

JimmyC

1
1

How to close existing connections to a DB

7 Answers7

Short Answer:

Long Answer:

Linked