Procedimiento almacenado para eliminar la conexión a la base de datos

Procedimiento almacenado SQL para eliminar todas las conexiones a la base de datos o servidor seleccionado excepto para el proceso que lo llama.

Introducción

Puede haber ocasiones en las que se necesite desconectar a todos los usuarios de la base de datos, un ejemplo es para la replicación de Log Shipping. Este código se utilizó en un servidor de informes que se actualizaba cada 20 minutos, ya que la tarea de restauración debía abrir la base de datos en modo exclusivo.

El código de abajo aceptará un nombre de base de datos, por lo que sólo necesita ser escrito una vez y almacenado en la base de datos maestra o de utilidades (si se ha creado una). Luego puede ser llamado desde cualquier otro proceso, en nuestro caso el plan de mantenimiento completo en el documento anterior a este.

Es un procedimiento almacenado bastante simple que simplemente obtiene una lista de las conexiones activas y hace un bucle a través de cada una eliminando el proceso y la conexión.

SQL Code

CREATE PROC maint.KillConnections(@database VARCHAR(50)) AS BEGIN

SET NOCOUNT ON;

DECLARE @spid INT

DECLARE @killstatement NVARCHAR(10)--Declare a cursor to select the users connected to the specified database

DECLARE c1 CURSOR FAST_FORWARD FOR SELECT request_session_id                    


FROM sys.dm_tran_locks 


WHERE resource_type='DATABASE' 


AND (DB_NAME(resource_database_id)=@database OR @database IS NULL)

OPEN c1

FETCH c1 INTO @spid

WHILE @@FETCH_STATUS= 0 BEGIN

      IF @@SPID<>@spid--Don't kill the connection of the user executing this statement

      BEGIN

            -- Construct dynamic sql to kill spid

            SET @killstatement ='KILL '+CAST(@spid AS VARCHAR(5))

            EXEC sp_executesql @killstatement

            PRINT @spid -- Print killed spid            


      END

      FETCH NEXT FROM c1 INTO @spid

END

-- Clean up

CLOSE c1

DEALLOCATE c1

END

GO