none
Logging replication

    Question

  • Hi guys,

    During the bulk copy data into tables in Replication Monitor I can see tables coming by which are copied from the publisher to the subscriber including the amount of rows. Is this somewhere logged so I can see what was copied? I mean I see some tables coming by I selected (for a customer) and show 0 rows. If this is logged somewhere I can show the customer that there are some (and point these by name) tables which are empty on both sides and ask them what the purpose is for replicating these.

    Otherwise I need to pay attention to the actions in the selected session and write the tables down on a piece of paper.

    kr
    Marco

    Friday, November 2, 2018 1:28 AM

Answers

  • Aha, what I was looking for is in the table
    [distribution database].[dbo].[MSdistribution_history] and or [MSlogreader_history]
    or any other _history table


    • Marked as answer by marqo Monday, November 5, 2018 6:41 PM
    • Edited by marqo Monday, November 5, 2018 7:04 PM
    Monday, November 5, 2018 6:41 PM

All replies

  • You can run the sp_browsereplcmds stored procedure against the distribution database to retrieve the transaction commands waiting to be propagated to the subscriber. It'll return the commands that are waiting on the distribution database to be applied to the subscriber. 

    You can also filter the specific article and database when calling the stored procedure.

    https://docs.microsoft.com/en-us/sql/relational-databases/replication/monitor/view-replicated-commands-and-information-in-distribution-database?view=sql-server-2017


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    Friday, November 2, 2018 4:44 AM
  • You can query msrepl_commands to get an idea of what articles are being replicated.  I log queries like this every 10 minutes to figure out what articles are generating all the replication traffic.  You will then need to figure out what is not being replicated. You will need to turn immediate sync off for this.

    select MSpublisher_databases.publisher_db,MSpublications.publication, MSarticles.source_object, count(*) count  ,max(entry_time), min(entry_time) From MSrepl_commands
    join MSpublisher_databases on MSrepl_commands.publisher_database_id=MSpublisher_databases.ID 
    join MSpublications on MSpublications.publisher_db=MSpublisher_databases.publisher_db
    join MSarticles on MSpublications.publication_id=MSarticles.publication_id
    join MSrepl_transactions on MSrepl_transactions.publisher_database_id=MSpublisher_databases.id
    and MSrepl_transactions.xact_seqno=MSrepl_commands.xact_seqno
    group by MSpublisher_databases.publisher_db,MSpublications.publication, MSarticles.source_object
    
     
    
     

    • Marked as answer by marqo Sunday, November 4, 2018 5:04 PM
    • Unmarked as answer by marqo Monday, November 5, 2018 6:38 PM
    Friday, November 2, 2018 12:22 PM
    Moderator
  • thanks this helps
    • Marked as answer by marqo Sunday, November 4, 2018 2:11 PM
    • Unmarked as answer by marqo Sunday, November 4, 2018 5:04 PM
    Sunday, November 4, 2018 2:11 PM
  • thanks this helps

    Hi Marqo,

    Please mark the response that answered your question. Looks like you marked your own response as the answer. Thanks.


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    Sunday, November 4, 2018 2:45 PM
  • Aha, what I was looking for is in the table
    [distribution database].[dbo].[MSdistribution_history] and or [MSlogreader_history]
    or any other _history table


    • Marked as answer by marqo Monday, November 5, 2018 6:41 PM
    • Edited by marqo Monday, November 5, 2018 7:04 PM
    Monday, November 5, 2018 6:41 PM