Procedimento armazenado para matar todas as conexões ao banco de dados selecionado

Procedimento armazenado SQL para matar todas as conexões ao banco de dados selecionado, exceto para a pessoa que o chama.

Introdução

Pode haver momentos em que você precisa desconectar todos os usuários do seu banco de dados, um exemplo é para replicação de Log Shipping. Este código abaixo foi usado em um servidor de relatórios que foi atualizado a cada 20 minutos, pois o banco de dados precisa ser aberto no modo exclusivo pela tarefa de restauração.

O código abaixo aceita um nome de banco de dados, portanto, ele só precisa ser escrito uma vez e armazenado no banco de dados mestre. Pode então ser chamado a partir de qualquer outro processo, no nosso caso o plano de manutenção completo.

SQL Code

Use [utilities]
GO
CREATE PROC [maint].KillConnections(@database varchar(50)) AS
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 for select request_session_id from sys.dm_tran_locks where resource_type='DATABASE' ANDDB_NAME(resource_database_id)= @database
open c1
fetch next from c1 into @spid
-- for each spid...
while @@FETCH_STATUS= 0
begin
      -- Don't kill the connection of the user executing this statement
      IF @@SPID<>@spid
      begin
            -- Construct dynamic sql to kill spid
            set @killstatement ='KILL '+cast(@spid as varchar(3))
            exec sp_executesql @killstatement
            -- Print killed spid
            print @spid
      end
      fetch next from c1 into @spid
end
-- Clean up
close c1
deallocate c1