SQL SERVER – Check the Isolation Level with DBCC useroptions

In recent consultancy project coordinator asked me – “can you tell me what is the isolation level for this database?” I have worked with different isolation levels but have not ever queried database for the same. I quickly looked up bookonline and found out the DBCC command which can give me the same details.

You can run the DBCC UserOptions command on any database to get few details about dateformat, datefirst as well isolation level.

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

I thought this was very handy script, which I have not used earlier. Thanks Gary for asking right question.

Note: This optional will only give you isolation for current connection where the command has ran and not at server level.

Reference: Pinal Dave (http://blog.sqlauthority.com)

19 thoughts on “SQL SERVER – Check the Isolation Level with DBCC useroptions

  1. DBCC USEROPTIONS returns the current settings for the user’s connections, not for a database.

    As for the original question, do databases have isolation levels? It’s connections that operate under a transaction isolation level, as I understand it.

    Like

    • Pinal, Can you comment on this. I understand that there is no isolation level for a database.

      We set isolation level at transation level or set a connection level.

      So every time, we open a transation, a connection / session level isolation value get applies to a transation.

      Correct me, if I am not correct .

      Like

  2. We expose this using sys.databases for SQL Server 2005 and above.

    Example:
    select
    CASE is_read_committed_snapshot_on
    WHEN 1 THEN ‘Read Committed Snapshot’
    ELSE ‘Read Committed’
    END as TransactionIsolationLevel
    from sys.databases
    where database_id = DB_ID()

    Like

  3. @Chris: Starting from SQL Server 2005, we introduced a row versioning. This required READ_COMMITTED_SNAPSHOT property of the database to be set to ON. This can be found out using DBCC USEROPTIONS. It can help you find out if row versioning is enabled for the database.

    Like

  4. Pingback: SQL SERVER – Appling NOLOCK Hint at Query Level – NOLOCK for whole Transaction Journey to SQLAuthority

  5. You may use this :

    select session_id, host_name, login_name, transaction_isolation_level from sys.dm_exec_sessions where original_login_name = ‘Your Account’

    for view the isolation level of a user.

    transaction_isolation_level:

    1 = ReadUncomitted

    2 = ReadCommitted

    3 = Repeatable

    4 = Serializable

    5 = Snapshot

    Like

  6. Pingback: SQL SERVER – Importance of ANSI ISOLATION Levels in SQL Server Database – Quiz – Puzzle – 1 of 31 « SQL Server Journey with SQLAuthority

  7. I want to perform bulk insert operation on a table . using BULK INSERT command in sql server. This bulk insert command enclosed in a Transaction.

    BEGIN TRANSACTION

    BULK INSERT ………
    …………………….

    COMMIT TRANSACTION

    if the bulk insert operation is performed by more than one user at the same time . then there is a deadlock occurs. How can I avoid this situation. which type of isolation level is needed?

    Like

  8. As referred in the Books Online, it is only at the current connection options. This is a wrong post. Please dont follow it.

    Like

  9. Hi Pinal,

    That was pretty useful. But as far as I have read, the Isolation Level can’t be changed at Server level, then i guess using this DBCC command in any connection will give the same restult ?

    Like

  10. Hello Pinal,

    While set the transaction isolation level,isolation level changed after refreshing same isolation level is coming. Please suggest me how to fix this issue. Means mssqlserver by default isolation level is Read committed . I was changed to Read uncommitted. But after refreshing it shows again Read committed.

    Like

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