Set Snapshot isolation is not reflecting

Question

When I ran - SET TRANSACTION ISOLATION LEVEL SNAPSHOT -Command successfully executed But the below query returns :

SELECT is_read_committed_snapshot_on,
       snapshot_isolation_state_desc,
       snapshot_isolation_state
FROM   sys.databases
WHERE  name = 'MyDB' 

Output:

is_read_committed_snapshot_on   0
snapshot_isolation_state_desc   OFF 
snapshot_isolation_state        0

Could you pleae let me know why the set transction command is not reflecting? Also, I assume ALLOW_SNAPSHOT_ISOLATION is already "on" as my "SET TRANSACTION ISOLATION LEVEL SNAPSHOT "command runs successfully.Is that correct?

Answer 1

No snapshot isolation is not on for that database.

If you try and then run a query against that database you will get the message.

Snapshot isolation transaction failed accessing database 'MyDB' because snapshot isolation is not allowed in this database. Use ALTER DATABASE to allow snapshot isolation.

To enable it you need to run

ALTER DATABASE MyDB SET ALLOW_SNAPSHOT_ISOLATION ON;