locked
How to change READ COMMITTED SNAPSHOT isolation level RRS feed

  • Question

  • Dear all,

    We are using SQL and Azure Service stack.

    We have one database that we have created and the we discover that it requires to use the READ COMMITTED SNAPSHOT isolation level.

    Due to that we try to change this parameter (which is 0 by default) using the following query :

    ALTER DATABASE <database name>
    SET READ_COMMITTED_SNAPSHOT ON
    WITH ROLLBACK IMMEDIATE;

    But we get an error that this parameter is unknown.

    I have try the same with an Azure SQL service and it works fine.

    Is there any way to set READ_COMMITTED_SNAPSHOT to on ?

    Thanks for help

    regards

    Thursday, November 7, 2019 1:51 PM

All replies

  • Hi Wakefun,

    What SQL Server version are you using? Because as far as I saw you were able to configure this in Azure sql DB but you didn't in other engine.

    Regarsd!

    Friday, November 8, 2019 3:54 PM
  • Hi Wakefun,

    This is being investigated internally but did want to add to the conversation. The snapshot functionality is based upon the OLE DB Driver:

    Working with Snapshot Isolation

    "The OLE DB Driver for SQL Server has enhancements that take advantage of the snapshot isolation introduced in SQL Server 2005 (9.x)."

    Azure Stack uses a resource provider adapter that handles all SQL connectivity and the following documentation provides an overview of how to deploy this service: Deploy the SQL Server resource provider on Azure Stack

    I do not have an Azure Stack environment available to investigate this as there is a need to understand if the OLE DB Driver is built into this resource provider adapter.

    So, I am reaching out to see if I can get you a verified answer.

    Regards,

    Mike

    Friday, November 15, 2019 7:58 PM
  • In reaching out internally, Azure Stack does not currently support `snapshot' functionality. Please comment on and up vote the User Voice entry: Bring Snapshot capability on Azure Stack

    Please let us know if you have any additional questions.

    Regards,

    Mike


    Monday, November 25, 2019 11:52 PM