Transação no Sql Server

Question

Pessoal me ajude.

Eu peguei um select na internet para verificar se tem alguma transação LIGADA no banco, alguém tem que executar Rollback ou Commit para fechar a transação, eu peguei o seguinte select:

SELECT  L.request_session_id AS SPID, 
        DB_NAME(L.resource_database_id) AS DatabaseName,
        O.Name AS LockedObjectName, 
        P.object_id AS LockedObjectId, 
        L.resource_type AS LockedResource, 
        L.request_mode AS LockType,
        ST.text AS SqlStatementText,        
        ES.login_name AS LoginName,
        ES.host_name AS HostName,
        TST.is_user_transaction as IsUserTransaction,
        AT.name as TransactionName,
        CN.auth_scheme as AuthenticationMethod
FROM    sys.dm_tran_locks L
        JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id
        JOIN sys.objects O ON O.object_id = P.object_id
        JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id
        JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id
        JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id
        JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id
        CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST
WHERE   resource_database_id = db_id()
ORDER BY L.request_session_id

Mais tem um problema:

Esse select só pega as transações que estão em execução.

Onde quero chegar, por exemplo ,digamos que executamos esta query abaixo:

begin tran

delete from clientes

Tabela Clientes com 40 milhões de clientes.

MAIS QUANDO EU CANCELO A QUERY E DEPOIS EXECUTO O SELECT LÁ DE CIMA PARA VERIFICAR TRANSAÇÃO ABERTA, ELE NÃO PEGA ESTA TRANSAÇÃO QUE FIZ, a base de dados continua "presa",ele não pega transação que é cancelada, então existe uma TRANSAÇÃO PENDENTE, é aí que eu não consigo verificar, entenderam?

Existe outra query para esta verificação de transação aberta?

No aguardo.

Answer 1

Este script roda continuamente procurando sessoes, e tambem te informa por email quando mata algum.

Veja se pode te ajudar:

DECLARE
	@ComandoKill nvarchar(max);
SET @ComandoKill = N'';

DECLARE
	@NumSecondsBloq int;

SET @NumSecondsBloq = 300; --> Coloque aqui a quantidade de segundos

WHILE 1 = 1
BEGIN

SELECT
	@ComandoKill = @ComandoKill + K.ComandoKill + ';' + CHAR(13)+CHAR(10)
FROM
(
	SELECT
		 'KILL '+CONVERT(varchar(30),s.session_id) as ComandoKill
		,S.session_id
		,S.login_name
		,S.host_name
		,S.last_request_start_time
		,DATEDIFF(SS,last_request_start_time,CURRENT_TIMESTAMP) as Tempo
		,CONVERT(bit,B.Total) as TemSessaoBloqueada
		,TST.transaction_id
	FROM
		sys.dm_exec_sessions S 
		INNER JOIN
		sys.dm_tran_session_transactions TST
			ON TST.session_id = s.session_id
		OUTER APPLY
		(
			select COUNT(*) as Total from sys.dm_exec_requests R WHERE R.blocking_session_id = S.session_id
		) AS B
	WHERE
		S.status = 'sleeping'
		AND
		S.session_id > 50
		AND
		DATEDIFF(SS, last_request_start_time,CURRENT_TIMESTAMP) >=  @NumSecondsBloq
		AND
		CONVERT(bit,B.Total) = 1
) K


	
IF ISNULL(LEN(@ComandoKill),0) > 0
BEGIN
	EXEC(@ComandoKill);
	
	DECLARE
		@EmailKill nvarchar(max)
	
	SET  @EmailKill = 'Foram detectadas sessões zumbis com mais de '+CONVERT(varchar(30),@NumSecondsBloq)+' segundos causando bloqueio. O seguinte comando foi executado para matá-las:'+
						CHAR(13)+CHAR(10)+
					    @ComandoKill
					    ;
	
	----> Aqui enviamos o email
	EXEC msdb.dbo.sp_send_dbmail
					 @profile_name = NULL
					,@recipients = 'COLOCAR O EMAIL AQUI '
					,@subject = 'MONITOR DE BLOQUEIO'
					,@body = @EmailKill
					,@body_format = 'TEXT'
				
	
	RAISERROR('',1,1) WITH NOWAIT;
	WAITFOR DELAY '00:00:05';
	
END ELSE
	WAITFOR DELAY '00:00:30';

END

---

[]'s | Rodrigo Ribeiro Gomes | MCTS/MCITP Dev/DBA