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

User · Answer

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

User · Answer

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      Here you can find the checkbox saying   close existing connections to destination database     Just check it  and you can proceed for the restore operation      It automatically will resume all connections after completion of the Restore

User · Answer

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

User · Answer

in restore wizard click  close existing connections to destination database   in Detach Database wizard click  Drop connection  item

User · Answer

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

User · Answer

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

User · Answer

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

[sql] How to close existing connections to a DB

Examples related to sql

Examples related to sql-server