locked
Snapshot Isolation RRS feed

Answers

  • Nothing will break in the sense "stop working", since snapshot isolation is opt-in only. However, to implement snapshot isolation, SQL Server adds 14 bytes to all rows on inserts and updates, so your database will grow a little more than if you do not enable snapshot. Also, to implement snapshot isolation, SQL Server maintains a version store in tempdb, so your tempdb will be bigger than before, and you should maybe be proactive to increase its size by 50% or so. (I took that number out of thin air.)

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Monday, February 11, 2019 10:42 PM
  • The following thread talks about the same error. 

    https://social.msdn.microsoft.com/Forums/en-US/d9286bbc-54a9-4293-909d-86baa493fd8e/sql-azure-data-sync-issue

    Here is an article that talks about the isolation level in Azure SQL DB and how to set a specific isolation level.

    https://social.technet.microsoft.com/wiki/contents/articles/1639.handling-transactions-in-windows-azure-sql-database.aspx

    As soon as you enable the "ALLOW_SNAPSHOT_ISOLATION" setting against the DB, the versions of data rows start to accumulate in the tempdb version store. Make sure you monitor the tempdb growth. A poorly written code could potentially hold up the uncommitted transactions and in turn, hold the version store cleanup and causing tempdb to grow excessively. For a more thorough explanation of the snapshot isolation level, head over to this article


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    Monday, February 11, 2019 11:07 PM
    Answerer

All replies

  • Nothing will break in the sense "stop working", since snapshot isolation is opt-in only. However, to implement snapshot isolation, SQL Server adds 14 bytes to all rows on inserts and updates, so your database will grow a little more than if you do not enable snapshot. Also, to implement snapshot isolation, SQL Server maintains a version store in tempdb, so your tempdb will be bigger than before, and you should maybe be proactive to increase its size by 50% or so. (I took that number out of thin air.)

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Monday, February 11, 2019 10:42 PM
  • The following thread talks about the same error. 

    https://social.msdn.microsoft.com/Forums/en-US/d9286bbc-54a9-4293-909d-86baa493fd8e/sql-azure-data-sync-issue

    Here is an article that talks about the isolation level in Azure SQL DB and how to set a specific isolation level.

    https://social.technet.microsoft.com/wiki/contents/articles/1639.handling-transactions-in-windows-azure-sql-database.aspx

    As soon as you enable the "ALLOW_SNAPSHOT_ISOLATION" setting against the DB, the versions of data rows start to accumulate in the tempdb version store. Make sure you monitor the tempdb growth. A poorly written code could potentially hold up the uncommitted transactions and in turn, hold the version store cleanup and causing tempdb to grow excessively. For a more thorough explanation of the snapshot isolation level, head over to this article


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    Monday, February 11, 2019 11:07 PM
    Answerer