unico - sql server change single user mode to multi user mode

Script para eliminar todas las conexiones a una base de datos(más de RESTRICTED_USER ROLLBACK)

Question

Tengo una base de datos de desarrollo que se vuelve a implementar con frecuencia desde un proyecto de base de datos de Visual Studio (a través de una compilación automática de TFS).

A veces, cuando ejecuto mi compilación recibo este error:

ALTER DATABASE falló porque no se pudo colocar un bloqueo en la base de datos ''MyDB''. Inténtalo más tarde.
La instrucción ALTER DATABASE falló.
No se puede eliminar la base de datos "MyDB" porque está actualmente en uso.

Intenté esto:

ALTER DATABASE MyDB SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE

pero todavía no puedo soltar la base de datos. (Creo que la mayoría de los desarrolladores tienen acceso dbo).

Puedo ejecutar SP_WHO manualmente y comenzar a eliminar conexiones, pero necesito una forma automática de hacerlo en la compilación automática. (Aunque esta vez mi conexión es la única en el DB que estoy tratando de dejar caer).

¿Existe un script que pueda eliminar mi base de datos independientemente de quién esté conectado?

Answer 1

@AlexK escribió una gran answer . Solo quiero agregar mis dos centavos. El código a continuación se basa completamente en la respuesta de @ AlexK, la diferencia es que puede especificar el usuario y una hora desde la última ejecución del lote (tenga en cuenta que el código usa sys.dm_exec_sessions en lugar de master..sysprocess):

DECLARE @kill varchar(8000);
set @kill =''''
select @kill = @kill + ''kill '' +  CONVERT(varchar(5), session_id) + '';'' from sys.dm_exec_sessions 
where login_name = ''usrDBTest''
and datediff(hh,login_time,getdate()) > 1
--and session_id in (311,266)    
exec(@kill)

En este ejemplo, solo se eliminará el proceso del usuario usrDBTest que se ejecutó el último lote hace más de 1 hora.

Answer 2

Debe tener cuidado con las excepciones durante los procesos de asesinato. Entonces puedes usar este script:

USE master;
GO
 DECLARE @kill varchar(max) = '''';
 SELECT @kill = @kill + ''BEGIN TRY KILL '' + CONVERT(varchar(5), spid) + '';'' + '' END TRY BEGIN CATCH END CATCH ;'' FROM master..sysprocesses 
EXEC (@kill)

Answer 3

He probado con éxito con un código simple a continuación

USE [master]
GO
ALTER DATABASE [YourDatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

Answer 4

La secuencia de comandos supremamente eficiente de Matthew se actualizó para usar el DMV dm_exec_sessions, reemplazando la tabla del sistema sysprocesses en desuso:

USE [master];
GO

DECLARE @Kill VARCHAR(8000) = '''';

SELECT
    @Kill = @Kill + ''kill '' + CONVERT(VARCHAR(5), session_id) + '';''
FROM
    sys.dm_exec_sessions
WHERE
    database_id = DB_ID(''<YourDB>'');

EXEC sys.sp_executesql @Kill;

Alternativa utilizando el bucle WHILE (si desea procesar cualquier otra operación por ejecución):

USE [master];
GO

DECLARE @DatabaseID SMALLINT = DB_ID(N''<YourDB>'');    
DECLARE @SQL NVARCHAR(10);

WHILE EXISTS ( SELECT
                1
               FROM
                sys.dm_exec_sessions
               WHERE
                database_id = @DatabaseID )    
    BEGIN;
        SET @SQL = (
                    SELECT TOP 1
                        N''kill '' + CAST(session_id AS NVARCHAR(5)) + '';''
                    FROM
                        sys.dm_exec_sessions
                    WHERE
                        database_id = @DatabaseID
                   );
        EXEC sys.sp_executesql @SQL;
    END;

Answer 5

Poco conocido: la instrucción GO sql puede tomar un número entero para la cantidad de veces que se repita el comando anterior.

Así que si usted:

ALTER DATABASE [DATABASENAME] SET SINGLE_USER
GO

Entonces:

USE [DATABASENAME]
GO 2000

Esto repetirá el comando USE 2000 veces, forzará el punto muerto en todas las demás conexiones y tomará posesión de la conexión individual. (Dando acceso exclusivo a su ventana de consulta para hacer lo que desee).

Answer 6

Puede obtener la secuencia de comandos que proporciona SSMS haciendo lo siguiente:

1. Haga clic derecho en una base de datos en SSMS y elija eliminar
2. En el cuadro de diálogo, marque la casilla de verificación "Cerrar conexiones existentes".
3. Haga clic en el botón Script en la parte superior del cuadro de diálogo.

El script se verá algo así:

USE [master]
GO
ALTER DATABASE [YourDatabaseName] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
DROP DATABASE [YourDatabaseName]
GO

Answer 7

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 8

Según mi experiencia, el uso de SINGLE_USER me ayuda la mayoría de las veces, sin embargo, hay que tener cuidado: he tenido ocasiones en las que, entre el momento en que inicio el comando SINGLE_USER y el momento en que finaliza ... aparentemente otro ''usuario'' había obtenido el Acceso a SINGLE_USER, no a mí Si eso sucede, te enfrentará a un difícil trabajo tratando de recuperar el acceso a la base de datos (en mi caso, fue un servicio específico que se ejecuta para un software con bases de datos SQL que obtuvo el acceso SINGLE_USER antes que yo). Lo que creo que debería ser la forma más confiable (no puedo garantizarlo, pero es lo que voy a probar en los próximos días), es en realidad: - detener los servicios que pueden interferir con su acceso (si los hay) - utilice el script ''kill'' anterior para cerrar todas las conexiones - establezca la base de datos en single_user inmediatamente después de eso - luego haga la restauración

Como suena eso ?

Answer 9

Actualizado

Para MS SQL Server 2012 y superior

USE [master];

DECLARE @kill varchar(8000) = '''';  
SELECT @kill = @kill + ''kill '' + CONVERT(varchar(5), session_id) + '';''  
FROM sys.dm_exec_sessions
WHERE database_id  = db_id(''MyDB'')

EXEC(@kill);

Para MS SQL Server 2000, 2005, 2008

USE master;

DECLARE @kill varchar(8000); SET @kill = '''';  
SELECT @kill = @kill + ''kill '' + CONVERT(varchar(5), spid) + '';''  
FROM master..sysprocesses  
WHERE dbid = db_id(''MyDB'')

EXEC(@kill); 

Answer 10

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

Ref: http://msdn.microsoft.com/en-us/library/bb522682%28v=sql.105%29.aspx