Olá pessoal…

Hoje vou mostrar como encerrar todas as conexões em uma database no SQL Server.

SQL Server

Esse procedimento pessoal, basicamente pega os endereços do SQL Server e “mata” a conexão deles, sem precisar utilizar comandos como ALTER DATABASE.

Abaixo o código fonte completo:

--Se a procedure existir, exclui
IF OBJECT_ID('dbo.sp_Kill', 'P') IS NOT NULL
	DROP PROCEDURE [dbo].[sp_Kill]
GO

--Cria a procedure e o parâmetro que ela recebe
CREATE PROCEDURE sp_Kill 
	@DBNAME VARCHAR(30)

AS 
BEGIN 
	
	SET NOCOUNT ON 
	DECLARE @SPID INT 
	DECLARE @STR NVARCHAR(50) 
	DECLARE @HOSTNAME NVARCHAR(50) 
	DECLARE @DBID INT 

	--Cria a tabela tempor[ár]
	CREATE TABLE #TMPLOG ( 
		SPID INT, 
		ECID INT, 
		STATUS VARCHAR(50), 
		LOGINAME VARCHAR(255), 
		HOSTNAME VARCHAR(50), 
		BLK INT, 
		DBNAME VARCHAR(30), 
		CMD VARCHAR(100) , 
		RID INT, 
	) 
	
	--Se não encontrar a database, mostra log de erro
	SELECT @DBID = DB_ID(@DBNAME) 
	IF @DBID IS NULL 
		PRINT 'Nao foi encontrado a database [' + @DBNAME + '], verificar o nome.' 
	
	--Inserir na tabela temporária, o próprio SPID para desconsiderar 
	INSERT INTO #TMPLOG EXEC SP_WHO 
	
	--Deleta tudo da TMPLOG que for abaixo de 50 (podem fazer parte de processos internos do SQL Server) 
	DELETE FROM #TMPLOG WHERE SPID < 50 
	IF @@ERROR <> 0 GOTO ERROR_HANDLE 

	--Declara o cursor para pegar os SPID dessa DataBase
	DECLARE CURPROCESSID CURSOR FOR SELECT SPID FROM #TMPLOG 
	WHERE DBNAME LIKE @DBNAME 
	OPEN CURPROCESSID 
	
	--Seleciona o SPID para ver se é válido
	FETCH NEXT FROM CURPROCESSID INTO @SPID 
	SELECT @HOSTNAME=HOSTNAME FROM #TMPLOG WHERE SPID=@SPID 
	
	--Verifica se há SPID para ser encerrado
	IF @SPID IS NOT NULL 
		PRINT 'Existem SPID para serem encerrado na database: ' + @DBNAME 
	ELSE 
		PRINT 'Nao existe SPID para serem encerrados na database ' + @DBNAME 

	--Percorrendo os dados
	WHILE @@FETCH_STATUS = 0 
	BEGIN 
		--Se for o mesmo SPID
		IF @SPID = @@SPID 
		BEGIN 
			PRINT 'O seu SPID nao pode ser encerrado, pulando ' + CONVERT(VARCHAR,@SPID) + ' - ' + @HOSTNAME 
		END 
		ELSE 
		BEGIN 
			--Executando o comando de Kill e imprimindo o SPID
			SET @STR = 'KILL ' + CONVERT(VARCHAR,@SPID) 
			EXEC (@STR)

			PRINT CONVERT(VARCHAR,@SPID) + ' - ' + @HOSTNAME 
		END
		
		--Se houve erro, pula para o ERROR_HANDLE
		IF @@ERROR <> 0 GOTO ERROR_HANDLE 
			FETCH NEXT FROM CURPROCESSID INTO @SPID 
	END 

	--Declaração do Ponteiro de Erro para mostrar mensagem
	ERROR_HANDLE:
	IF @@ERROR <> 0 PRINT 'Erro ao encerrar o SPID - ' + CONVERT(VARCHAR,@SPID) + ' - ' + @HOSTNAME 

	--Dropa tabela temporária
	DROP TABLE #TMPLOG 
	SET NOCOUNT OFF 
END 

Obs.: Antes de executar em produção, faça um teste em uma base de testes.

Referência: https://www.sqlservercentral.com/Forums/1315827/Kill-All-Spids-according-to-Database-Name

Bom pessoal, por hoje é só.
Abraços e até a próxima.

Tags: Banco de DadosSQL Server