Solved

Replication - commands applied to Subscriber

Posted on 2009-07-15
13
1,075 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
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

856 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