locked
sys.dm_tran_active_snapshot_database_transactions and tempdb and checkpoint RRS feed

  • Question

  • Hi,

    I have recently made the default isolation level for all queries on my database Read Committed Snapshot.  And notice the sys.dm_tran_active_snapshot_database_transactions  result in long running transactions that are not handled properly in code.  They are simple select statements that run for hours.  

    Would these long running transactions cause tempdb to grow even if it is simple recovery model?  I mean from normal 1GB to 50 GB in a day?

    Also would that affect checkpoint from normally running under second to taking 10 minutes?

     

    Thanks

    Thursday, November 3, 2011 8:50 PM

Answers

  • Hi gao.seng,

    >>Would these long running transactions cause tempdb to grow even if it is simple recovery model?  I mean from normal 1GB to 50 GB in a day?
    The long running transactions can prevent the shrinking of version store data in tempdb. In this case, the tempdb will grow up, and the size depends on the number active transactions and the time of the active transaction holding.

    For more information:
    http://blogs.msdn.com/b/sqlserverfaq/archive/2010/10/13/troubleshooting-tempdb-growth-due-to-version-store-usage.aspx
    http://www.sqllion.com/2009/05/monitoring-tempdb-in-sql-server-2005

    >>Also would that affect checkpoint from normally running under second to taking 10 minutes?
    The number of dirty pages can influence the time required for the checkpoint. For automatic checkpoint or manually checkpoint without specifying the checkpoint_duraion, SQL Server will adjust frequency to issue a checkpoint to minimize the performance impact on the operating system. You can specify the duration time in seconds to complete the operation for manually running the checkpoint.

    For more information:
    http://msdn.microsoft.com/en-us/library/ms188748.aspx

    Best Regards,
    Stephanie Lv


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    • Marked as answer by gao.seng Monday, November 7, 2011 2:41 PM
    Monday, November 7, 2011 3:19 AM