patternsqlMinor
SQL Server transaction replication ignores DML which does not change data
Viewed 0 times
sqlwhichdatareplicationignoresdmltransactiondoesservernot
Problem
I have a transactional replication setup between the two servers and I noticed that if a run a statement similar to this:
UPDATE mytable
SET mycolumn = mycolumn
the replication somehow knows to ignore this transaction and it does not get applied on the subscriber. I have confirmed it by running SQL Profiler and also by adding TIMESTAMP column to my subscriber table (it does not change). I suspect there is some sort of mechanism, which enables this kind of "smart" behavior and I was wondering if anybody could shed some light on it.
Thank you!
UPDATE mytable
SET mycolumn = mycolumn
the replication somehow knows to ignore this transaction and it does not get applied on the subscriber. I have confirmed it by running SQL Profiler and also by adding TIMESTAMP column to my subscriber table (it does not change). I suspect there is some sort of mechanism, which enables this kind of "smart" behavior and I was wondering if anybody could shed some light on it.
Thank you!
Solution
Tranactional Replication works by reading the transaction log.
This kind of "non updating update" often won't generate any transaction log records.
See Paul White's article The Impact of Non-Updating Updates for more about this.
This kind of "non updating update" often won't generate any transaction log records.
See Paul White's article The Impact of Non-Updating Updates for more about this.
Context
StackExchange Database Administrators Q#57500, answer score: 6
Revisions (0)
No revisions yet.