Como encerrar todas as conexões de uma DataBase no SQL Server

Como encerrar todas as conexões de uma DataBase no SQL Server

Olá pessoal…

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

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.

About Dan_Atilio

Analista e desenvolvedor de sistemas. Técnico em Informática pelo CTI da Unesp. Graduado em Banco de Dados pela Fatec Bauru. Entusiasta de soluções Open Source e blogueiro nas horas vagas. Autor do projeto Terminal de Informação, onde são postados tutoriais e notícias envolvendo o mundo da tecnologia.

Deixe uma resposta

%d blogueiros gostam disto: