Solved

Replication - commands applied to Subscriber

Posted on 2009-07-15
13
1,061 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
  • 5
13 Comments
 
LVL 37

Expert Comment

by:momi_sabag
Comment Utility
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
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 1

Author Comment

by:LukeB
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
ModernMatt
Maybe leave the Q and my solution? Might help others?
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

762 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now