Check transactin isolation level

Option 1:

DBCC USEROPTIONS

Set Option                 Value
————————– ———————————————-
textsize                   2147483647
language                   us_english
dateformat                 mdy
datefirst                      7
lock_timeout               -1
quoted_identifier          SET
arithabort                     SET
ansi_null_dflt_on          SET
ansi_warnings              SET
ansi_padding               SET
ansi_nulls                     SET
concat_null_yields_null    SET
isolation level            read committed

Option 2:

SELECT
    CASE transaction_isolation_level
        WHEN 1 THEN ‘READ UNCOMMITTED’
        WHEN 3 THEN ‘REPEATABLE READ’
        WHEN 4 THEN ‘SERIALIZABLE’
        WHEN 5 THEN ‘SNAPSHOT’
        ELSE CASE WHEN is_read_committed_snapshot_on = 0
                THEN ‘READ COMMITTED’
                ELSE ‘READ COMMITTED SNAPSHOT’
            END
    END
    
FROM    
    sys.dm_exec_requests AS r INNER JOIN sys.databases AS d ON r.database_id = d.database_id
WHERE
    session_id = @@SPID
    AND r.database_id= DB_ID()

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s