SQL SERVER – Check the Isolation Level with DBCC useroptions
- by pinaldave
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.
Reference: Pinal Dave (http://blog.sqlauthority.com)
Filed under: Pinal Dave, SQL, SQL Authority, SQL Query, SQL Scripts, SQL Server, SQL System Table, SQL Tips and Tricks, T SQL, Technology Tagged: Transaction Isolation