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

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

select is_read_committed_snapshot_on, *
from sys.databases

About these ads
  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 )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 401 other followers

%d bloggers like this: