How to see the isolation level

-- Method:1
dbcc useroptions with no_infomsgs;  -- see the last "set option"
go

-- Method:2
select case transaction_isolation_level when 1 then 'Read Uncommitted'
            when 2 then 'Read Committed'
            when 3 then 'Repeatable Read'
            when 4 then 'Serializable'
            when 5 then 'Snapshot'
            else 'Unspecified'
     end As [Isolation level]
from sys.dm_exec_sessions where session_id = @@SPID;
go

-- Method:3
In SQL Profiler, in the Audit:login event show the isolation level set by the connecting provider (see last line):

Audit Login Event:

-- network protocol: LPC
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed

However, if distributed transactions are used there may be a different isolation level in use which is not readily visible in SQL profiler Login audit. In such cases you can query the DMV per session to see the current isolation level, or run the command DBCC USEROPTIONS.

Reference: http://msdn.microsoft.com/en-us/library/ms173763%28v=sql.100%29.aspx

Comments

Popular Posts