[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Replication - commands applied to Subscriber

Posted on 2009-07-15
13
Medium Priority
?
1,102 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
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!

 
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

650 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