ROWVERSION/TIMESTAMP skipping rows problem

ROWVERSION/TIMESTAMP skipping rows problem

Do you use ROWVERSION/TIMESTAMP to pull changed data?
If you do, you might experience a problem – not all changed rows are pulled, some are skipped. How can that be, if we pulled entire range of rowverion changes with no gaps?

Let me illustrate it in this video with demo inside:

Here is a demo script, and a way how to properly pull data without skipping rows:

Do not use @@DBTS to pull rows! Use MIN_ACTIVE_ROWVERSION() instead. I hope this helped someone.

5 Comments on “ROWVERSION/TIMESTAMP skipping rows problem

  1. Thanks Verdan

    We’ve been using MIN_ACTIVE_ROWVERSION() quite successfully too. Except, today we noticed an unfortunate behavour when using Read-only replicas (we’re using SQL 2017).

    Quite surprisingly the value of MIN_ACTIVE_ROWVERSION() is much LARGER on read-only replicas than on the primary database – this doesn’t make sense and obviously causes problems as you can end up missing a while lot of records.

    Any ideas about getting a “true” active row version from read-only replicas?

    • Are both primary and secondary giving the same @@DBTS value? If you stop activity (changes) on primary, is MIN_ACTIVE_ROWVERSION() on both giving you the same value? Due to transaction activity MIN_ACTIVE_ROWVERSION() gives lower value than @@DBTS. Even on secondary replica if you use MIN_ACTIVE_ROWVERSION that should not skip any rows, despite being ahead of primary, if you pull always from the same (secondary) replica.

  2. Well presented. I didn’t even know about @@DBTC but makes sense it wouldn’t be reliable. I just save last pulled value per table and save it and use it for next pull for starting point. Seems like use of default read committed isolation level seems like you can’t leak changes correct?

    • If you use MIN_ACTIVE_ROWVERSION, it will be ok in both default isolation (read committed) or when RCSI is enabled. If you use @@DBTS, it will be incorrect, skiping rows.

  3. Hi
    Perhaps you can help me with my similar question. I have a database in which a table with a field data type is timestamp, I noticed one feature that I still cannot understand. When I make a backup of this database and restore it on another server, I observe the following behavior, after inserting a new row into the table, the value of the timestamp field is less than in the last data row present

Leave a Reply

Your email address will not be published. Required fields are marked *


This site uses Akismet to reduce spam. Learn how your comment data is processed.