Post reply

Version Store isolation levels confusion

  • October 10, 2012 at 4:38 am

    #282986

    Hi All

    I used the below query to assess which databases have produced records in the version store

    select distinct db_name(database_id) from sys.dm_tran_version_store

    The result shows me one database.

    Then I executed the below script to check the settings for this database

    select name, snapshot_isolation_state_desc, is_read_committed_snapshot_on from sys.databases order by name

    The problem is that this particular database has both snapshot_isolation_state_desc and

    is_read_committed_snapshot_on turned off.

    How else could this database be producing Version Store records?

    Thanks

  • October 10, 2012 at 4:55 am

    #1547584

    Isolation Level can be handled by session level also, not only database level, if I am right.

    Is there any chance your application issue that?

  • October 10, 2012 at 5:20 am

    #1547598

    dbasql79 (10/10/2012)

    Isolation Level can be handled by session level also, not only database level, if I am right.

    Is there any chance your application issue that?

    You can use snapshot isolation level by session but then allow_snapshot_isolation needs to be turned on for the database.

  • October 10, 2012 at 7:02 am

    #1547637

    Triggers use the version store. So do online index creation and rebuild.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 10, 2012 at 7:13 am

    #1547641

    GilaMonster (10/10/2012)

    Triggers use the version store. So do online index creation and rebuild.

    Thanks Gail

    Is there a way that I can check how the contents of my version store arrived there, whether trigger or indexing etc.?

    I've rebuilt an index online on a test system and I don't see any results when selecting from sys.dm_tran_version_store. Am I looking in the correct place?

    Thanks

  • October 10, 2012 at 7:19 am

    #1547644

    Yup, you're looking in the right place, if you looked during the rebuild. No need to keep the versions around afterwards.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • April 8, 2013 at 4:28 pm

    #1604597

    I am running an upgrade that modifies a couple of very large tables that have multiple indexes. I do not need versioning for this and it is forcing the tempdb database size to grow to overly large. If an index rebuild fails during this upgrade I can rebuild the index later.

    I had thought that turning it off for the database that is being upgraded would take care of the problem but it didn't.

    Is it possible to force the versioning off during an index rebuild?

  • April 8, 2013 at 4:45 pm

    #1604603

    When rebuilding an index, the Version store is only used when ONLINE option is ON.

  • April 8, 2013 at 4:49 pm

    #1604604

    I guess this upgrade is using the "online" option . It doesn't give me a chance to tell it how to do the index build.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply