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

 

Date Time Convert -Format

SELECT CONVERT(VARCHAR(20), GETDATE(), 100)
Feb 18 2013 11:29AM (Mon DD YYYY HH:MM AM/PM)
SELECT CONVERT(VARCHAR(20), GETDATE(), 101)
02/18/2013 (MM/DD/YYYY)
SELECT CONVERT(VARCHAR(20), GETDATE(), 102)
2013.02.18 (YYYY.MM.DD)
SELECT CONVERT(VARCHAR(20), GETDATE(), 103)
18/02/2013 (DD/MM/YYYY)
SELECT CONVERT(VARCHAR(20), GETDATE(), 104)
18.02.2013 (DD.MM.YYYY)
SELECT CONVERT(VARCHAR(20), GETDATE(), 105)
18-02-2013 (DD-MM-YYYY)
SELECT CONVERT(VARCHAR(20), GETDATE(), 106)
18 Feb 2013 (DD Mon YYYY)
SELECT CONVERT(VARCHAR(20), GETDATE(), 107)
Feb 18, 2013 (Mon DD, YYYY)
SELECT CONVERT(VARCHAR(20), GETDATE(), 108)
11:37:14 (HH:MM:SS)
SELECT CONVERT(VARCHAR(20), GETDATE(), 109)
Feb 18 2013 11:37:53 (Mon DD YYYY HH:MM:SS)
SELECT CONVERT(VARCHAR(20), GETDATE(), 110)
02-18-2013 (MM-DD-YYYY)
SELECT CONVERT(VARCHAR(20), GETDATE(), 111)
2013/02/18 (YYYY/MM/DD)
SELECT CONVERT(VARCHAR(20), GETDATE(), 112)
20130218 (YYYYMMDD)
SELECT CONVERT(VARCHAR(20), GETDATE(), 113)
18 Feb 2013 11:40:18 (DD Mon YYYY HH:MM:SS)
SELECT CONVERT(VARCHAR(20), GETDATE(), 114)
11:40:54:040 (HH:MM:SS:MMM)24 hr
SELECT CONVERT(VARCHAR(20), GETDATE(), 120)
2013-02-18 11:53:32 (YYYY-MM-DD HH:MM:SS)
 

MSSQLSERVER Event Description:Replication-Replication Distribution Subsystem: agent scheduled for retry. Query timeout expired

Step 1:-  Check the locks on the DB
Step2 :- In my case it was one ALTER and Another INSERT Command.
Step3:- Make sure you check all blocked SPID's
Step4:- Once you resolve the blocking it will workfine.