none
TempDB Version Store Cleanup RRS feed

  • Question

  • Hi All,

    I'm reading about tempdb version store cleanup and wanted to confirm something. We are using SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT set to ON.

    Our tempdb has grown to 60GB and the version store is 56GB.

    We have old, open transactions (based on sys.dm_tran_active_snapshot_database_transactions) for almost 3 weeks!!

    I killed them (6 of them), and now our version store is back to 8GB.

    Questions:

    1) Is version store cleanup based on the oldest open transaction? Meaning, if I had those transactions open for 3 weeks, then no version cleanup can occur beyond 3 weeks of row versioning.

    OR is Version store cleanup based on the objects within the oldest open transaction. Meaning, I killed 5 of the transactions and the version store did not shrink. I killed the 6th and it shrunk. Was the cleanup based on the objects of that 6th transaction (i.e. I had 48GB worth of row versioning for the objects involved in the 6th transaction)?

    I tend to believe that cleanup is based on #1 since i'm trying to figure out where to investigate the root cause first - should I look at the fact that I had 6 tx (coming from 6 different servers) with old open transactions, OR do I focus on the 6th transaction only?

    Thanks in advance!

    Friday, December 13, 2013 7:21 PM

Answers

  • Hello,

    Is version store cleanup based on the oldest open transaction? Meaning, if I had those transactions open for 3 weeks, then no version cleanup can occur beyond 3 weeks of row versioning.

    Correct. About every minute the version store cleanup is run. The cleanup can only remove versions that are no longer needed are do not rely on older versions. What I mean by this, for example, is that is a transaction is open for your 3 weeks and it is the oldest version - any newer version cannot be cleaned up on that table as they all rely on the oldest used one.

    You may want to monitor the version store which can be done from the performance counters.


    Sean Gallardy | Blog | Twitter

    Saturday, December 14, 2013 11:21 PM
    Answerer

All replies

  • Hello,

    Is version store cleanup based on the oldest open transaction? Meaning, if I had those transactions open for 3 weeks, then no version cleanup can occur beyond 3 weeks of row versioning.

    Correct. About every minute the version store cleanup is run. The cleanup can only remove versions that are no longer needed are do not rely on older versions. What I mean by this, for example, is that is a transaction is open for your 3 weeks and it is the oldest version - any newer version cannot be cleaned up on that table as they all rely on the oldest used one.

    You may want to monitor the version store which can be done from the performance counters.


    Sean Gallardy | Blog | Twitter

    Saturday, December 14, 2013 11:21 PM
    Answerer
  • Im in a similar boat, considering the use of optimistic locking.  This is respect to Microsoft CRM, which uses SQL as a backend database and several optimization strategies suggest to use snapshot isolation

    Anyone finding this thread, here are some source info ive been reading

    http://msdn.microsoft.com/en-us/library/tcbchxcb(v=vs.110).aspx
    http://sqlblogcasts.com/blogs/gavinpayneuk/archive/2012/05/11/read-committed-snapshot-isolation-two-considerations.aspx
    http://technet.microsoft.com/en-us/sqlserver/gg545007.aspx
    http://www.brentozar.com/archive/2013/01/implementing-snapshot-or-read-committed-snapshot-isolation-in-sql-server-a-guide/

    The reason Im resurrecting this thread, is that my question is similar:
    If I see that tempdb is growing and the version store has old transactions in it, how can they be cleaned up safely?



    • Edited by shiftbit Tuesday, May 13, 2014 11:41 PM gkghjk
    Tuesday, May 13, 2014 11:26 PM
  • Shiftbit,

    If I see that tempdb is growing and the version store has old transactions in it, how can they be cleaned up safely?

    There is nothing you can do to directly clean up the version store. The only course of action that can really be taken is to find the transaction(s) that are the oldest which are generally holding the oldest version store records. Since the version store can't clean up any newer ones until all of the generations behind it are cleaned up.


    Sean Gallardy | Blog | Twitter

    Wednesday, May 14, 2014 12:53 PM
    Answerer
  • This may not be the best way, but first I check for the oldest open, active transactions:

    select hostname,elapsed_time_seconds,session_id, is_snapshot, blocked, lastwaittype, cpu, physical_io,  open_tran, cmd 
    from sys.dm_tran_active_snapshot_database_transactions a
    join master..sysprocesses b
    on a.session_id=b.spid 
    order by a.elapsed_time_seconds desc

    Then ill kill the session_id one by one starting at the oldest and monitor the version store size (if its going down) using this:

    SELECT SUM(version_store_reserved_page_count) AS [version store pages used], (SUM(version_store_reserved_page_count)*1.0/128) AS [version store space in MB] 
    FROM sys.dm_db_file_space_usage; 

    Wednesday, May 14, 2014 4:01 PM
  • How do you clean up old transactions or free them up?
    Wednesday, May 14, 2014 5:31 PM
  • We are facing same issues. We killed oldest session with version store.

    Tempdb has free space inside the files after killing the process.

    But we are unable to shrink the files since version store is still being used for new sessions.

    How to move active pages from end of the files to beginning of the tempdb files?

    Wednesday, August 7, 2019 8:40 AM