• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1070
  • Last Modified:

Which rows changed in merge replication?

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
colinspurs
Asked:
colinspurs
  • 2
  • 2
1 Solution
 
AnujSQL Server DBACommented:
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
 
colinspursAuthor Commented:
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
 
AnujSQL Server DBACommented:
That you can get form sysmergearticles

SELECT nickname
FROM SysMergeArticles
0
 
colinspursAuthor Commented:
Brilliant, thanks again. I found it really hard to get help on the web.

Cheers,

   Col
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now