Replication - commands applied to Subscriber

I hava tranactional replication setup with subscriber updates between 2 machines. The machines are in the same domain but the connectoin is between cities and is somewhat slow. Push replcn. SQLserver 2008 Std Edtn

The replicaiton is working ok but is taking +5 hrs. I can see on the "Undistributed Commands" tab that there are +38.000 commends in in the Distriutor waiting to be applied to the Subscriber.

Questions 1 : How do I find the list of commands that are waiting to be done? I want to see which tables are changing so much between machines so I can modify to reduce the replcn time needed.

Questions 2 : I thought the reason was foreign keys relationships between a master and child tables. But I have reset the foreign keys to be "Enforce For Replication = No". And the foreign key was not setup on the Subscriber. So it does not seem to be a cascade INSERT and UPDATE that is taking the time?
LVL 1
LukeBAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

momi_sabagCommented:
you can see the pending commands with msrepl_showcmds (or something like that)...
regarding what is taking the time, i put start the profile on the subscriber and see which statements take long time
0
LukeBAuthor Commented:
Hi Momi

ok, I can not find msrepl_showcmds ?

can you explain more the second sentence, I do not know what 'start the profile on the subscriber' is ?
0
Bob LearnedCommented:
I believe, but don't quote me (I am not a DBA), that you can see that in replication agent jobs with Management Studio:

How to: View and Modify Replication Agent Command Prompt Parameters (SQL Server Management Studio)
http://msdn.microsoft.com/en-us/library/ms151326(SQL.90).aspx
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

LukeBAuthor Commented:
Learned One
that shows you how to view the command prompt params for agent jobs
 i.e. Job, Job Properties, (then say) Run Agent, Step :
-Publisher [BEVSQL02] -PublisherDB [dbname] -Distributor [BEVSQL02] -DistributorSecurityMode 1  -Continuous
what I want to find or see is what TSQL commands are being sent from the Publisher to the Subscriber. i.e. TSQL DELETE or UPDATE or INSERT statements so I can see what data is being changed
0
LukeBAuthor Commented:
Momi
msrepl_showcmds only shows the commands pending between the Publisher and Distributor. Because the Pub and Distributor are the same machine those commands happen very fast and transact without me being able to see them. Anyway, what I want to see is the transactions between Distributor and Subscriber
0
LukeBAuthor Commented:
I worked it out -
say you have a parent table with a child table(s) and referential integrity with cascade update and cascade delete.
Then you update, say, all the records in the parent (some field in the parent that you update all records on) then replication will delete the parent records on the subscriber and re-add them. Because of ref integ it will ALSO delete all the child records on the subscriber and re-add them.
I have 12000 records on the parent and for each of those maybe 100 child records each. If my users update the parent several times a day then the parent and the child tables get deleted and re-filled many times on the subscriber. And since it is transactional replication, each transaction on the subscriber has to be done. So a big long process.
The solution was to move those fields on the parent that I was allowing to be bulk updated to another table and thus the parent table does not get a holis bolis update.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LukeBAuthor Commented:
ModernMatt
Maybe leave the Q and my solution? Might help others?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.