Procedimento armazenado para eliminar todas as conexões com o banco de dados ou servidor selecionado
Introdução
Pode haver momentos em que você precise desconectar todos os usuários de seu banco de dados, um exemplo é para a replicação de envio de log. Este código abaixo foi utilizado em um servidor de relatórios que era atualizado a cada 20 minutos, pois o banco de dados precisa ser aberto em modo exclusivo pela tarefa de restauração.
O código a seguir aceitará um nome de banco de dados, portanto, ele só precisa ser escrito uma vez e armazenado no banco de dados mestre ou utilitários (se você tiver criado um). Ele pode então ser chamado a partir de qualquer outro processo, no nosso caso o plano de manutenção completo no documento acima.
É um procedimento armazenado bastante simples que simplesmente obtém uma lista das conexões ativas e faz um loop em cada uma, eliminando o processo e a conexão.
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
