Skip navigation

Log in to create and rate content, and to follow, bookmark, and share content with other members.

How to Determine if Versioning is Enabled on SQL Database

Discussion created by Harriet_Kim_1764 on Apr 7, 2017
Latest reply on Oct 19, 2017 by Christian_Boeck_57

Like • Show 0 Likes0
Comment • 1

Scenario:
A user's AE database on the MS SQL server is experiencing deadlocks.

Solution:
Verify if versioning is enabled:

SELECT is_read_committed_snapshot_on FROM sys.databases WHERE name= 'YourDatabaseName'

To enable:

alter database database name set READ_COMMITTED_SNAPSHOT ON

For more details, refer to the documentation.

Outcomes

1 Reply

Christian_Boeck_57 Oct 19, 2017 7:28 AM
Another important point to mention is that with SQL Server 2016 Versioning and Snapshot Isolation is disabled and you need activate it in order to load the Initial Data.
otherwise you will receiver the error message:

U00038042 Enabling Row Versioning-Based Isolation Level.
U00038284 Row versioning for read-committed and snapshot transactions is disabled.

Please execute the following statement to solve the issue:

ALTER DATABASE 'YourDatabaseName' SET READ_COMMITTED_SNAPSHOT ON; ALTER DATABASE 'YourDatabaseName' SET ALLOW_SNAPSHOT_ISOLATION ON;
Like • Show 0 Likes0
Actions

Recommended Content