In SQL SERVER 2008
============================================================
Step 1 :-
SELECT is_read_committed_snapshot_on FROM
sys.databases WHERE name= 'DATABASENAME'
OutPUT :-
1
= READ_COMMITTED_SNAPSHOT option is ON. Read operations under the
read-committed isolation level are based on snapshot scans and do not acquire
locks.
0
= READ_COMMITTED_SNAPSHOT option is OFF (default). Read operations under the
read-committed isolation level use share locks.
Step 2 :- If its 0, run this
to make it on.
ALTER
DATABASE DATABASENAME SET
READ_COMMITTED_SNAPSHOT ON
In SQL SERVER 2012
=========================================================
Step 1:- Right Click on the DB choose properties.
Step 2:- Choose options, go to miscellaneous section
-> IS READ COMMITTED… -> Make it true.
CHECKING TRANSACTION LEVEL
=======================================================
Step 1 :-
DBCC USEROPTIONS
Step 2:-
SET TRANSACTION ISOLATION LEVEL READ COMMITTED