Home > DBA Stuff > SQL DBA – Check Isolation Level of a Database | DBCC USEROPTIONS

SQL DBA – 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]


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.
Check this:

select is_read_committed_snapshot_on, *
from sys.databases

Categories: DBA Stuff Tags:
  1. No comments yet.
  1. No trackbacks yet.

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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: