ERROR:CRJAZ2336E The SQL server database is configured incorrectly. The database might experience performance problems and become deadlocked
One answer
--> One of the steps that is required to be done during SQL Server database setup is to enable row versioning on the database instances.
Following is the command to enable row versioning for a database instance 'jts':
*****
ALTER DATABASE jts SET READ_COMMITTED_SNAPSHOT ON
GO
*****
--> If row versioning on the database instance is not enabled, then users might receive the following error message when they run diagnostics in the jts/admin page of ccm/admin page:
*****
ERROR:CRJAZ2336E The SQL server database is configured incorrectly.The database might experience performance problems and become
deadlocked.
*****
--> Here are the steps to verify if row versioning is not enabled for any of the database instances:
*****
Run the following SQL command for all the database instances:
- Open a sql commandline editor.
- Then:
For JTS Database, run the following command: (Assuming jts is the name of the database. Please replace jts below with the appropriate db
name for jts)
***
USE jts
SELECT is_read_committed_snapshot_on FROM sys.databases WHERE name='jts'
GO
***
Similarly, run the above command replacing jts with appropriate database instances/names for ccm, qm, rm and dw databases.
Example:
*****
- For CCM database: (Assuming ccm is the name of the database. Please replace ccm below with the appropriate db name for ccm)
USE ccm
SELECT is_read_committed_snapshot_on FROM sys.databases WHERE name='ccm'
GO
- FOR QM database: (Assuming qm is the name of the database. Please replace qm below with the appropriate db name for qm)
USE qm
SELECT is_read_committed_snapshot_on FROM sys.databases WHERE name='ccm'
GO
- FOR RM database: (Assuming rm is the name of the database. Please replace rm below with the appropriate db name for rm)
USE rm
SELECT is_read_committed_snapshot_on FROM sys.databases WHERE name='rm'
GO
- FOR dw database: (Asuming dw is the name of the database. Please replace dw below with the appropriate db name for dw)
USE dw
SELECT is_read_committed_snapshot_on FROM sys.databases WHERE name='dw'
GO
*****
--> The output for the commands above will be as shown below:
*****
A)
> 1> USE jts;
> 2> SELECT is_read_committed_snapshot_on FROM sys.databases WHERE
name='jts'
> 3> GO
> -----------------------------
> 1
>
> (1 rows affected)
>
> B)
> 1> USE ccm;
> 2> SELECT is_read_committed_snapshot_on FROM sys.databases WHERE
name='ccm'
> 3> GO
> -----------------------------
> 1
>
> (1 rows affected)
>
> C)
> 1> USE qm;
> 2> SELECT is_read_committed_snapshot_on FROM sys.databases WHERE
name='ccm'
> 3> GO
> -----------------------------
> 1
>
> (1 rows affected)
>
> D)
> 1> USE rm;
> 2> SELECT is_read_committed_snapshot_on FROM sys.databases WHERE
name='rm'
> 3> GO
> -----------------------------
> 0
>
> (1 rows affected)
>
> E)
> 1> USE dw;
> 2> SELECT is_read_committed_snapshot_on FROM sys.databases WHERE
name='dw'
> 3> GO
> -----------------------------
> 0
>
> (1 rows affected)
*****
--> The output boolean value 0 or 1 represents if the row versioning is disabled or enabled respectively.
In the example output above, row versioning is diabled for rm and dw database instances as the output is 0.
--> The following Microsoft article provides details on how to modify and enable row versioning:
*****
http://msdn.microsoft.com/en-us/library/ms175095.aspx
*****