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 (https://blog.sqlauthority.com)

, ,
Previous Post
SQLAuthority News – Virtual Launch Event for Office 2010 – Contest – Win MS Office License
Next Post
SQLAuthority News – Microsoft SQL Server 2008 R2 – PowerPivot for Microsoft Excel 2010

Related Posts

19 Comments. Leave new

  • Chris Randall
    May 25, 2010 12:34 am

    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.

    Reply
    • 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 .

      Reply
  • Amit Banerjee
    May 25, 2010 1:02 am

    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()

    Reply
  • Amit Banerjee
    May 26, 2010 2:45 am

    @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.

    Reply
  • Stefano Antonelli
    April 20, 2011 6:24 pm

    I prefer use this :

    SELECT name, snapshot_isolation_state_desc, is_read_committed_snapshot_on FROM sys.databases

    Reply
  • gsravanthkumar
    August 11, 2011 1:07 pm

    Hi,

    How to find which isolation will be used in command prompt ?
    and How should we found it?

    Reply
  • Stefano Antonelli
    September 5, 2011 5:16 pm

    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

    Reply
  • Change the isolation level to Snapshot isolationlevel.

    Reply
  • I want to set the isolation level as read uncommited for a table so that it will never get locked. can we do that?

    Reply
  • 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?

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

    Reply
  • 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 ?

    Reply
  • if you want to check default isolation level of your database then use,DBCC UserOptions

    Reply
  • 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.

    Reply
  • How to change the ANSI_PADDING option on existing table using ALTER table?

    Reply
  • very useful command. thanks for sharing

    Reply

Leave a Reply

Menu