sql - sesiones - Cómo cerrar las conexiones existentes a una base de datos

ver conexiones activas sql server 2008

Question

Quiero cerrar las conexiones existentes a un servidor MS SQL para que pueda hacer una restauración en esa base de datos programáticamente.

Answer 1

Esto debería desconectar a todos los demás y dejarlo como el único usuario:

alter database YourDb set single_user with rollback immediate

Nota: No lo olvides

alter database YourDb set MULTI_USER

¡Después de que termines!

Answer 2

Lo encontré aquí: 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

Answer 3

Puedes usar Cursor así:

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

Escribí sobre eso en mi blog aquí: http://www.pigeonsql.com/single-post/2016/12/13/Kill-all-connections-on-DB-by-Cursor

Answer 4

Solución perfecta proporcionada por 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

Answer 5

en el asistente de restauración, haga clic en "cerrar conexiones existentes a la base de datos de destino"

en el Asistente de separación de la base de datos, haga clic en el elemento "Cerrar conexión".