2

I am trying to resolve the following issue and need your help.

We wanted to turn on is_read_committed_snapshot_on and Snapshot_isolation_state_desc ON on our db!

When I ran following command to check current status of both above values, I got

snapshot_isolation_state_desc = ON & is_read_committed_snapshot_on = 0.

After this I ran two ALTER commands,

ALTER DATABASE [<databasename>]
SET READ_COMMITTED_SNAPSHOT ON

ALTER DATABASE <Your crm database>
SET ALLOW_SNAPSHOT_ISOLATION ON

The values updated to following,

snapshot_isolation_state_desc  = ON & 
is_read_committed_snapshot_on = 1. 

So we did some test we still got some locks but they we different type not the SHARED LOCK. after that I wanted to turn OFF and see if we go back to normal or not.

and I ran,

 ALTER DATABASE [<databasename>]
    SET READ_COMMITTED_SNAPSHOT off

    ALTER DATABASE <Your crm database>
    SET ALLOW_SNAPSHOT_ISOLATION off

and we got,

snapshot_isolation_state_desc  = OFF & 
is_read_committed_snapshot_on = 0. 

And we wanted to turn it ON again and leave it in ON status I ran,

ALTER DATABASE [<databasename>]
SET READ_COMMITTED_SNAPSHOT ON

ALTER DATABASE <Your crm database>
SET ALLOW_SNAPSHOT_ISOLATION ON

BUT the thing is, it is still showing the following values,

**snapshot_isolation_state_desc  = OFF &** 
is_read_committed_snapshot_on = 1. 

and that's my issue, that why it's still in OFF status. I restarted SQL Services many times restarted App. Server but no luck.

Please help.

Thanks.

1

I found another way to turn ON snapshot_isolation_state_desc . Go to your DB, right click go to properties and find "Options" under Select a page on left side and then look for "Miscellaneous" option and you will see the first variable names as "Allow Snapshot Isolation" and just select the "True" and hit OK. it should be good to go. When I am running following query now to select the status, I get following.

snapshot_isolation_state_desc  = ON & 
is_read_committed_snapshot_on = 0. 

enter image description here

| improve this answer | |

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