find repl article which is taking longer time to sync

  • transaction replication is synching up

    replication monitor say undistrited transactions 4442941 and apply time 5 hr 34 min.

    I would like to find out the culprit article/transaction which is taking longer time.

    how to get that? any tables exits in sql? any script available? kindly help.

    and also I could see undistrited transactions are increasing gradually. Is it normal? or issue with replication? syncing not working? Please let me know.

    thanks

  • Need a little more information here...

    Is this synching happening as the result of the first time you turned on replication (i.e. the very first sync) or has it been set up for some time and it's just falling behind?

    replication monitor say undistrited transactions 4442941 and apply time 5 hr 34 min.

    If it's just falling behind, depending on the churn in your tables on the publisher it's possible that there's just a lot of changes being replicated (for instance, if you have a pricing table that gets updated daily with pricing information for your entire DB...it can cause a lot of replication commands to be pushed to your subscriber - this is normal). If it's just taking a long time as the result of your very first sync it's possible that you have contention on your disk subsystem, or issues within the network (after all you are copying bcp files back into the subscriber)...and so forth...

    I would like to find out the culprit article/transaction which is taking longer time.

    and also I could see undistrited transactions are increasing gradually. Is it normal? or issue with replication? syncing not working? Please let me know.

    It "can" be normal, but again it depends on your configuration - however, it shouldn't last too long. The time you're given is based upon performance and RARELY ends up being the actual time it takes to replicate the undistributed commands.

    This will help you get your undelivered transactions, so the ones that have more undelivered ones would be the DB tables generating the most activity.

    SELECT DISTINCT

    a.article, md.agent_name, ds.UndelivCmdsInDistDB, ds.DelivCmdsInDistDB

    FROM dbo.MSdistribution_status ds

    JOIN dbo.MSarticles a ON

    a.article_id = ds.article_id

    JOIN dbo.MSreplication_monitordata md ON

    md.agent_id = ds.agent_id

    WHERE UndelivCmdsInDistDB > 0

    ORDER BY UndelivCmdsInDistDB DESC

    You can also use this code to insert the replcommands into a temp table and examine it (note you can filter the procedure by each individual article, or use no param at all and load the entire batch)

    IF OBJECT_ID(N'tempdb.dbo.#repltmp') IS NOT NULL

    BEGIN

    DROP TABLE #repltmp

    END

    CREATE TABLE #repltmp

    (

    xact_seqno varbinary(16) NULL,

    originator_srvname sysname NULL,

    originator_db sysname NULL,

    article_id int NULL,

    [type] int NULL,

    partial_command bit NULL,

    hashkey int NULL,

    originator_publication_id int NULL,

    originator_db_version int NULL,

    originator_lsn varbinary(16) NULL,

    command nvarchar(max) NULL,

    command_id int

    )

    INSERT INTO #repltmp

    EXEC sp_browsereplcmds @article_id = 7

    SELECT command

    FROM #repltmp

    ORDER BY xact_seqno

    You can also this code to determine the pending count across your entire subscription (just replace with the X's with your infomation): EXEC distribution.dbo.sp_replmonitorsubscriptionpendingcmds

    @publisher ='X-YOUR-SERVER',

    @publisher_db = 'X-YOUR_PUBLISHED-DB',

    @publication ='X-NAME-OF-PUBLICATION',

    @subscriber ='x_SUBSCRIBER-SERVER',

    @subscriber_db ='X-SUBSCRIBER-DB',

    @subscription_type ='0' --0 for push and 1 for pull

    Not sure if answers all your questions but hopefully it will help point you in the proper direction

    ______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

  • Thank you.

    Syncing is happening not for the first tme after the outage.

    The scripts you gave--what is target server/database to execute?

    thanks for your help.

    Lastly, undistributed transactions are same for both subscribers but sync estimate time is different in replication monitor. why so? is there any specific reason for this?

  • #1 you run those on the distributor (they won't work anywhere else anyway)

    #2 While I can't say 100%, this is most likely caused by the limitations of you replication setup. That's why In the other article you posted I made reference to temporarily stopping a log reader agent or two

    ______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

  • Found this transaction replication article from the URL as useful and therefore listing the same for your benefit since you are essentially looking to monitor and conclude on what is not working well :

    http://www.sqlservercentral.com/blogs/basits-sql-server-tips/2012/07/25/t-sql-script-to-monitor-transactional-replication-status/[/url]

  • I got the article list by running below query.

    Now I want one (or a set of ) primary keys that replication considers is not replicated everywhere.. is there any way to acheive this? thanks

    This will help you get your undelivered transactions, so the ones that have more undelivered ones would be the DB tables generating the most activity.

    SELECT DISTINCT

    a.article, md.agent_name, ds.UndelivCmdsInDistDB, ds.DelivCmdsInDistDB

    FROM dbo.MSdistribution_status ds

    JOIN dbo.MSarticles a ON

    a.article_id = ds.article_id

    JOIN dbo.MSreplication_monitordata md ON

    md.agent_id = ds.agent_id

    WHERE UndelivCmdsInDistDB > 0

    ORDER BY UndelivCmdsInDistDB DESC

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply