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
- DBA Stuff (15)
- Interview Q (6)
- MS BI (7)
- Others (27)
- SQL Server Internals (54)
- SQL Server Versions (54)
- T SQL (81)
- Passed 70-461 Exam : Querying Microsoft SQL Server 2012
- CUBE, ROLLUP, COMPUTE, COMPUTE BY, GROUPING SETS
- Microsoft Azure Redis Cache - General availability
- Using OUTPUT Parameters in Stored Procedures
- DIRTY reads and PHANTOM reads
- Best Practices while creating Stored Procedures
- SQL Server 2012 does not support Linked Server to SQL Server 2000, workaround
- Stored Procedures vs Functions (Difference between SP & UDF)
- TSQL Interview Questions - Part 1
- UDF | User Defined Functions - Scalar, Table Valued (ITV), MultiSelect (MTV)
- 732,633 hits
StatCounter …since April’2012
This is my personal blog site.
The opinions expressed here represent my own and not those of my employer. For accuracy and official reference refer to MS Books On Line and/or MSDN/TechNet.
My employer do not endorse any tools, applications, books, or concepts mentioned on the blog. I have documented my personal experience on this blog.