Philippsen's Blog

Everyday findings in my world of .net and related stuff

SQL server row versioning

Posted by Torben M. Philippsen on November 4, 2010

A client of mine experienced some performance issues with their Dynamics AX setup. In order to analyse the problem I had to determine whether row versioning was enabled or not. Below I will show how determine whether row versioning es enabled or not and how to enable it.

If You want to know more about row versioning, please visit this msdn article about “SQL Server 2005 Row Versioning-Based Transaction Isolation”.

Determining whether row versioning is enabled:

use tempdb
SELECT snapshot_isolation_state, snapshot_isolation_state_desc, is_read_committed_snapshot_on
from sys.databases
where name = ‘my_database_name’

Enable row versioning:

use my_database_name
ALTER DATABASE my_database_name
    SET READ_COMMITTED_SNAPSHOT off;

ALTER DATABASE my_database_name
    SET ALLOW_SNAPSHOT_ISOLATION off;

Advertisements

Sorry, the comment form is closed at this time.

 
%d bloggers like this: