3

I want the default Transaction Isolation level to be SNAPSHOT.

How do I set this on a SQL Server 2016 database?

0
10

It is not possible to configure Snapshot Isolation (SI) as the default isolation level.

To use SI, the database must be enabled for Snapshot Isolation:

ALTER DATABASE CURRENT
SET ALLOW_SNAPSHOT_ISOLATION ON;

Then each connection must explicitly request SI, using for example:

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

It is possible to set the default Read Committed isolation level to use row versioning (RCSI).

If Read Committed Snapshot Isolation (RCSI) is sufficient for your purposes, the setting is:

ALTER DATABASE CURRENT
SET READ_COMMITTED_SNAPSHOT ON; 

Further reading:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.