View Transaction Isolation Levels

A query that lists every session with its current transaction isolation level.

Published Feb 8, 2009

Who is connected, and at what isolation level? This query lists every session with its host, login and current transaction isolation level decoded into readable names - useful for spotting a rogue Read Uncommitted or Snapshot session. A commented second query shows how to dig into active snapshot transactions.

SELECT
	session_id,
	host_name,
	login_name,
    CASE transaction_isolation_level
	   WHEN 0 THEN 'Unspecified'
	   WHEN 1 THEN 'ReadUncomitted'
	   WHEN 2 THEN 'Readcomitted'
	   WHEN 3 THEN 'Repeatable'
	   WHEN 4 THEN 'Serializable'
	   WHEN 5 THEN 'Snapshot'
	   ELSE '<Other>'
    END AS IsolationLevel
FROM
	sys.dm_exec_sessions
	
--SELECT * FROM sys.dm_tran_active_snapshot_database_transactions