MS SQL database

Snapshot Isolation

How to get enaio redline requires its database to have a snapshot isolation level of "read committed". With the statements in the following two codeblocks you can determine the current snapshot isolation level (1st codeblock) and set the level to the required "read committed" (2nd codeblock).

Dtermine the current snapshot isolation level.

SELECT is_read_committed_snapshot_on, 
       snapshot_isolation_state_desc, 
       snapshot_isolation_state 
FROM sys.databases
WHERE name = '<dbname>'

Power on Set snapshot isolation to "read committed"

ALTER DATABASE <database> SET SINGLE_USER WITH ROLLBACK IMMEDIATE ;
ALTER DATABASE <database> SET allow_snapshot_isolation ON;
ALTER DATABASE <database> SET READ_COMMITTED_SNAPSHOT ON;
ALTER DATABASE <database> SET MULTI_USER;

Transaction level

How to get the current transaction level.

SELECT CASE transaction_isolation_level
WHEN 0 THEN 'Unspecified'
WHEN 1 THEN 'ReadUncommitted'
WHEN 2 THEN 'ReadCommitted'
WHEN 3 THEN 'Repeatable'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL
FROM sys.dm_exec_sessions
where session_id = @@SPI