[SQL Server] Afficher la liste des transactions SQL ouvertes

  • Imprimer

--Afficher les transactions sql ouvertes

USE master
GO
SELECT spid,
PROGRAM_NAME,
nt_userName,
loginame,
DB_NAME(s.dbid) AS DatabaseName,
CR.TEXT AS Query
FROM sysprocesses s
CROSS apply sys.Dm_exec_sql_text(sql_handle) CR
WHERE open_tran = 1
-- Requêtes bloquées et session bloquante
SELECT r.session_id, r.blocking_session_id, r.wait_type, r.wait_time, t.text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.blocking_session_id <> 0;

-- Sessions avec une transaction ouverte sur la base
SELECT s.session_id, s.login_name, s.host_name, s.program_name,
       s.status, s.open_transaction_count, s.last_request_end_time
FROM sys.dm_exec_sessions s
WHERE s.open_transaction_count > 0
  AND s.database_id = DB_ID('MaBase');

 

-- Plus ancienne transaction active
DBCC OPENTRAN('MaBase');