Changing READ COMMITED VALUE ON DB

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