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:

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

Tagged with: , , , ,

2 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.

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.