Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 608
  • Last Modified:

Replication issue

Hi,

I have configured transactional replication on my servers.
Log reader agent is moving transactions from publisher to distributor correctly.
but for some reason the transaction from distributor to subscriber is taking a long time,
Undistributed commands from distributor to subscriber is high
and the estimated time to apply the transaction to subscriber is also increasing.
How can i push this transactions to the subscriber.

Please advise.
0
Sonali P
Asked:
Sonali P
1 Solution
 
TempDBACommented:
Check the server status of the subscriber. I mean to say in terms of CPU, memory, blocking,etc. If the subscriber health is good then you can run a profiler on the replication stored procedure to find out what time it is taking and why with the help of the plan.
0
 
ZberteocCommented:
Make sure that on the publisher you have all the indexes you need on the tables and in the replication properties you specify to replicate all the indexes from publisher, foreign keys are not necessaary, to subscriber. After that reinitialize your transaction, execute the snapshot agent. Also you can run n a

EXEC sp_who2 active

command on the subscriber and see if there are some ongoing processes that take long or if there is any blocking. You have to solve those.
0
 
Jim P.Commented:
Also what version of SQL are you using, and what OS?

Select @@Version

Open in new window

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Jim P.Commented:
Can you give us an idea of the resolution?
0
 
ZberteocCommented:
That is not enough my friend. People here spent time to give you decent answers so just saying the issue is solved won't work. Maybe you can give some more details on this. After all what you owe are just some points not money.
0
 
Sonali PDatabase AdministratorAuthor Commented:
Hi,

Log reader agent showed some undistributed transactions which were not marked for
replication.
I guess that was the reason the latency was increasing.
I simply dropped the subscription and the target database,
Rebuild all the indexes at the publisher side.
Created a new publication and subscrber and and it seems to have resolved the issue.


Thanks all for your help...

.
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

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