Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Which rows changed in merge replication?

Posted on 2013-10-31
4
Medium Priority
?
951 Views
Last Modified: 2013-10-31
I am merge replicating between two SQL 2008 R2 servers.   These are hardly touched, yet when I did a synchronisation, I had loads of updates.

How can I find out which rows were changed?

Thanks,

  Colin
0
Comment
Question by:colinspurs
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 15

Accepted Solution

by:
Anuj earned 2000 total points
ID: 39613743
Merge replication tracks the dml changes by using Insert, update and delete triggers, these changes are tracked in tables MSMerge_tombstone (For Deletes) MSMerge_Contents (For Update and Inserte) and MSMerge_GenHistory  (Group of changes). Replicated tables has rowguid column, so when a row changes its rowguid is tracked in corresponding MSMerge_tombstone , MSMerge_Contents (depending on Delete\Insert\Update) and MSMerge_GenHistory tables.

Following query determine the changed rows and in which container they exists (In MSMerge_Contents)

select <YourTable>.*
from <YourTable>
inner join MSmerge_contents on tCustomers.rowguid = MSmerge_contents.rowguid
inner join MSmerge_genhistory on MSmerge_contents.generation = MSmerge_genhistory.generation


Below code finds the pending changes in merge replication.

select <YourTable>.*
from <YourTable>
inner join MSmerge_contents on tCustomers.rowguid = MSmerge_contents.rowguid
inner join MSmerge_genhistory on MSmerge_contents.generation = MSmerge_genhistory.generation
where MSmerge_genhistory.genstatus = 0
0
 
LVL 3

Author Comment

by:colinspurs
ID: 39613852
That's very helpful, thanks.  How can I find which tables Tablenick refers to in MSmerge_contents?

Also, to find which database updated which (publisher or subscriber) I should run the queries on both databases?

Colin
0
 
LVL 15

Expert Comment

by:Anuj
ID: 39613957
That you can get form sysmergearticles

SELECT nickname
FROM SysMergeArticles
0
 
LVL 3

Author Closing Comment

by:colinspurs
ID: 39614420
Brilliant, thanks again. I found it really hard to get help on the web.

Cheers,

   Col
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question