Check Isolation Level of a Database | DBCC USEROPTIONS
While testing a functionality we had to run some Stored Procedures in parallel. So we executed all 4 SPs in different Sessions (separate windows). All SPs got executed successfully except one, this one ended up in a Deadlock.
I thought to check the Isolation level of database but my mind didn’t clicked instantly.
So I checked MS BOL about this and find the DBCC management command to find it, which is DBCC USEROPTIONS & as follows:
USE [AdventureWorks2012] GO DBCC USEROPTIONS GO
Output:- 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
Other Isolation level values it returns are:- - read uncommitted - read committed - repeatable read - serializable - read committed snapshot - snapshot
The sys.databases metadata View also contains a column i.e. is_read_committed_snapshot_on, which tells if READ_COMMITTED_SNAPSHOT Isolation level in ON or OFF.
select is_read_committed_snapshot_on, * from sys.databases