1

We have 4 SQL Servers engaged in Transactional Peer to Peer Replication. I am doing some work on the Indexes and have noticed 2 things I didn't expect:

  1. The Indexes have different fragmentation levels - I understand that only the Insert/Update/Delete commands are replicated - but wouldn't they therefore create the same level of fragmentation on each server? (They're not wildly different, but enough to catch the eye and change the order of priority (i.e. 99% at the top etc))

  2. The Log Files are wildly different sizes (the smallest is 10Gb the largest around 85Gb)- again, I was expecting them to be similar/spot on the same, surely if the log has recorded and performed the same actions due to the replication they would be similar?

Any thoughts/wisdom?

Thanks

MH

1 Answer 1

0

but wouldn't they therefore create the same level of fragmentation on each server?

No. Fragmentation is physical, replication is logical. The same sequence of insert/delete/update may produce different physical indexes, but transnational replication may not even produce the same sequence of insert/deletes/updates. Even with non-filtered publications, the replication agent can fail, rollback and retry when applying the changes.

surely if the log has recorded and performed the same actions due to the replication they would be similar

Not at all. The database activity may extend well beyond the area covered by replication (in other words, the database may contain more than just the subscription articles). But, more importantly, the log structure is shaped more by backup schedule and log retention than by activity. Read more here.

1
  • Thanks Remus. The 4 Replication servers are actually replicating full databases - so there shouldn't be a tremendous difference in articles/activity, but I take your point about the packaging of the original Commands vs Replicated Commands and both your answers are logical. I recently started the role here, so I can't comment on historic Backup schedules etc. (Ps. thanks for the link - very well written!)
    – MHSQLDBA
    Nov 7, 2014 at 9:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.