Cerrar conexiones SQL – Base de datos en uso

He encontrado varios metodos para aplicar, estos son:

1.- PROBADO

USE master GO ALTER DATABASE database_name SET OFFLINE WITH ROLLBACK IMMEDIATE GO

2.- SIN PROBAR

DECLARE @kill varchar(8000) = »; SELECT @kill = @kill + ‘kill ‘ + CONVERT(varchar(5), spid) + ‘;’ FROM master..sysprocesses WHERE dbid = db_id(‘MyDB’) EXEC(@kill);

3.- SIN PROBAR

CREATE PROCEDURE dbo.KillConexiones
@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 ‘Eliminando ‘+RTRIM(@cnt)+’ procesos.’

WHILE @spid IS NOT NULL
BEGIN
PRINT ‘Eliminando Proceso ‘+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)+’ Procesos por eliminar.’
END
END
GO

4.- SIN PROBAR –  PARECIDO AL 3 PERO SIN SER SP

USE MASTER
GO
DECLARE @spid SMALLINT
DECLARE @KILL VARCHAR(16)

DECLARE @dbname sysname
SET @dbname = 'Suscripciones'
dbid = db_id(@dbname) 
DECLARE kill_cursor CURSOR FOR
select spid
from master..sysprocesses
where status = 'sleeping'
AND dbid=57
OPEN kill_cursor
FETCH NEXT FROM kill_cursor INTO @spid
WHILE @@FETCH_STATUS = 0
      BEGIN
      SET @KILL= 'kill '+cast(@spid as varchar(5))
      print (@KILL)

FETCH NEXT FROM kill_cursor INTO @spid

END

5.- PROBADO OK


USE master
GO
SET NOCOUNT ON
DECLARE @DBName varchar(50)
DECLARE @spidstr varchar(8000)
DECLARE @ConnKilled smallint
SET @ConnKilled=0
SET @spidstr = ''
Set @DBName = 'NOMBRE_BASE_DE_DATOS'
IF db_id(@DBName) < 4 BEGIN PRINT 'Connections to system databases cannot be killed' RETURN END SELECT @spidstr=coalesce(@spidstr,',' )+'kill '+convert(varchar, spid)+ '; ' FROM master..sysprocesses WHERE dbid=db_id(@DBName) IF LEN(@spidstr) > 0
BEGIN
EXEC(@spidstr)
SELECT @ConnKilled = COUNT(1)
FROM master..sysprocesses WHERE dbid=db_id(@DBName)
END