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.

closed as off-topic by Erik Darling, hot2use, RDFozz, LowlyDBA, Marco Feb 20 '18 at 12:34

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Erik Darling, hot2use, RDFozz, LowlyDBA, Marco
If this question can be reworded to fit the rules in the help center, please edit the question.

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

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