Solved

Replication - commands applied to Subscriber

Posted on 2009-07-15
13
1,084 Views
Last Modified: 2012-05-07
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?
0
Comment
Question by:LukeB
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
13 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 24857896
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
 
LVL 1

Author Comment

by:LukeB
ID: 24857914
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
 
LVL 96

Expert Comment

by:Bob Learned
ID: 24878054
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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 1

Author Comment

by:LukeB
ID: 24918684
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
 
LVL 1

Author Comment

by:LukeB
ID: 24918705
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
 
LVL 1

Accepted Solution

by:
LukeB earned 0 total points
ID: 24985178
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
 
LVL 1

Author Comment

by:LukeB
ID: 24985188
ModernMatt
Maybe leave the Q and my solution? Might help others?
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question