July 27, 2012 at 8:29 pm
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
July 27, 2012 at 10:52 pm
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
July 28, 2012 at 12:37 am
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?
July 28, 2012 at 1:43 am
#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
July 28, 2012 at 2:39 am
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]
July 28, 2012 at 5:20 pm
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