How to check the isolation level of another connection SQL Server 2008

sql-server, sql-server-2008

Solution

SELECT 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' 
                  END 
FROM sys.dm_exec_sessions 
WHERE session_id = <spid_of_other_session>

Problem

I need to see the isolation level of all the current connections to find some locking issue. I tried `DBCC Useroptions` but it gives me info only for my user. I tried `DBCC PSS(0)` or `DBCC PSS(1,57)` But I get the following error: Incorrect DBCC statement. Check the documentation for the correct DBCC syntax and options.

Original source