Help-"jammed" transactional replication

Alex B

unread,

Feb 11, 2004, 9:00:06 AM2/11/04

to

I have one publisher with two push subscriptions on the same server using
transactional replication. When I update the table that is being replicated
(I issued a command that updated 23,000 records), the push subscriptions
lock up their table and never complete. I've waited literally hours! I've
kicked everyone else out of the database and tried it. When I look at the
progress, the latency was something like 4 million milliseconds (not
kidding). I tried running sp_who and sp_lock. sp_who showed that no one
else was in the database, but sp_lock showed at one point 140,000 locks...
When I stopped the distribution agents for those two push subscriptions, the
locks all went away.

I also tried restarting the server and trying it ... also with no luck.
Does anyone have any advice? The thing that I really don't want to have to
do is to recreate the subscription because the table is massive and the
users would kill me. Arggghhh... It seems like 23,000 record update (and it
only updated one field) should only take a couple of seconds at most!

Paul Ibison

unread,

Feb 11, 2004, 9:56:07 AM2/11/04

to

Haven't seen this before, but you could try reducing
Distributor
-CommitBatchSize
-CommitBatchThreshold
Log Reader
-MaxCmdsInTran (assuming >= sp1)
Details of these parameters in on MSDN
(http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/replsql/replmon_94kz.asp)
Regards,
Paul Ibison

Alex B

unread,

Feb 11, 2004, 10:13:01 AM2/11/04

to

I guess I should have said that I have tried the defaults, but also tried
going down to a batch size of 1, a batch commit of 1, a
maxdeliveredtransactions of 1... historyverboselevel of 3... I just tried
these again - kicked everyone out and in 4 minutes it didn't process one
transaction! I then stop/started it and then retried it with the defaults
and the same thing. I ended up with 90,000 or so locks all that look
similar to this:

96 9 1769773362 9 KEY (0c01573f25a7) X GRANT
96 9 1769773362 10 KEY (300161d0a5d4) X GRANT
96 9 1769773362 6 KEY (8c01102ee093) X GRANT
96 9 1769773362 6 KEY (5d0219859c66) X GRANT
96 9 1769773362 3 KEY (af0166991f61) X GRANT
96 9 1769773362 6 KEY (6103eaec077a) X GRANT
96 9 1769773362 11 KEY (7f0169e223d0) X GRANT
96 9 1769773362 12 KEY (110194c7d940) X GRANT
96 9 1769773362 0 RID 1:138823:0 X GRANT
96 9 1769773362 0 RID 1:138819:8 X GRANT
96 9 1769773362 7 KEY (fe011b73e42a) X GRANT
96 9 1769773362 5 KEY (7700881cd3b2) X GRANT
96 9 1769773362 7 KEY (a601823e8a73) X GRANT
96 9 1769773362 11 KEY (1602d75ebb95) X GRANT
96 9 1769773362 7 KEY (710168f572ec) X GRANT
96 9 1769773362 0 RID 1:201755:20 X GRANT
96 9 1769773362 255 RID 1:236341:51 X GRANT
96 9 1769773362 5 KEY (60015d20367b) X GRANT
96 9 1769773362 10 KEY (1b01faa14b41) X GRANT
96 9 1769773362 0 PAG 1:199720 IX GRANT
96 9 1769773362 13 KEY (bb016225fe97) X GRANT
96 9 1769773362 10 KEY (c5018eda7f88) X GRANT
96 9 1769773362 0 PAG 1:96351 IX GRANT
96 9 1769773362 0 RID 1:138820:0 X GRANT
96 9 1769773362 0 PAG 1:52053 IX GRANT
96 9 1769773362 5 KEY (af001cf3b235) X GRANT
96 9 1769773362 10 KEY (6b017555efb2) X GRANT
96 9 1769773362 5 KEY (a901f06bd7da) X GRANT
96 9 1769773362 5 KEY (7401c8c1a2f8) X GRANT
96 9 1769773362 6 KEY (6d034014ed61) X GRANT
96 9 1769773362 7 KEY (b401c9122e2b) X GRANT
96 9 1769773362 4 KEY (da0235e18f57) X GRANT
96 9 1769773362 0 RID 1:119594:13 X GRANT
96 9 1769773362 3 KEY (66016f2815cf) X GRANT
96 9 1769773362 3 KEY (6b0254e13cbf) X GRANT
96 9 1769773362 0 RID 1:138821:0 X GRANT
96 9 1769773362 13 KEY (6601c0c1b334) X GRANT
96 9 1769773362 4 KEY (0b03fed292c6) X GRANT
96 9 1769773362 11 KEY (540279f4570f) X GRANT
96 9 1769773362 6 KEY (0b03fed292c6) X GRANT
96 9 1769773362 10 KEY (1b025d6b649d) X GRANT
96 9 1769773362 0 PAG 1:207970 IX GRANT
96 9 1769773362 3 KEY (15027e730b99) X GRANT
96 9 1769773362 11 KEY (b001f6573cf2) X GRANT
96 9 1769773362 6 KEY (46034de5007a) X GRANT
96 9 1769773362 13 KEY (4a02b316c304) X GRANT
96 9 1769773362 5 KEY (ac0016a9dbbc) X GRANT
96 9 1769773362 7 KEY (8201b7921b65) X GRANT
96 9 1769773362 7 KEY (53026dcbadbb) X GRANT
96 9 1769773362 3 KEY (2702fb6e50bd) X GRANT
96 9 1769773362 9 KEY (a101b8798b8a) X GRANT
96 9 1769773362 0 PAG 1:52059 IX GRANT
96 9 1769773362 0 PAG 1:138821 IX GRANT
96 9 1769773362 4 KEY (840201eff463) X GRANT
96 9 1769773362 0 RID 1:138818:16 X GRANT
96 9 1769773362 0 PAG 1:207981 IX GRANT
96 9 1769773362 5 KEY (530191d1d88c) X GRANT
96 9 1769773362 4 KEY (150329350ce5) X GRANT
96 9 1769773362 0 PAG 1:192410 IX GRANT
96 9 1769773362 0 RID 1:201758:4 X GRANT
96 9 1769773362 11 KEY (41014590cd91) X GRANT
96 9 1769773362 9 KEY (5c01b245878e) X GRANT
96 9 1769773362 13 KEY (5601d5d1dce8) X GRANT
96 9 1769773362 10 KEY (4602f1a8283a) X GRANT
96 9 1769773362 11 KEY (ec01804907ce) X GRANT
96 9 1769773362 11 KEY (c701e02386a7) X GRANT
96 9 1769773362 0 PAG 1:228569 IX GRANT
96 9 1769773362 0 PAG 1:52058 IX GRANT
96 9 1769773362 0 PAG 1:138820 IX GRANT
96 9 1769773362 0 RID 1:138819:16 X GRANT
96 9 1769773362 7 KEY (0a013adcb478) X GRANT
96 9 1769773362 7 KEY (fb01f9da5e34) X GRANT
96 9 1769773362 14 KEY (ad01baefdd20) X GRANT
96 9 1769773362 6 KEY (b50242485a5d) X GRANT
96 9 1769773362 0 PAG 1:207980 IX GRANT
96 9 1769773362 6 KEY (ad0309406318) X GRANT
96 9 1769773362 0 RID 1:201759:4 X GRANT
96 9 1769773362 5 KEY (e1008ffdd8c5) X GRANT
96 9 1769773362 7 KEY (bf00e674a3d3) X GRANT
96 9 1769773362 0 PAG 1:196543 IX GRANT
96 9 1769773362 13 KEY (e001ebb9c881) X GRANT
96 9 1769773362 10 KEY (5f015a17cdeb) X GRANT
96 9 1769773362 13 KEY (4d021b24121c) X GRANT
96 9 1769773362 5 KEY (3a013a8add3d) X GRANT
96 9 1769773362 7 KEY (a600d2cc5d4d) X GRANT
96 9 1769773362 9 KEY (e70158d94a14) X GRANT
96 9 1769773362 3 KEY (ba0117c462e0) X GRANT
96 9 1769773362 0 PAG 1:228570 IX GRANT
96 9 1769773362 9 KEY (e3017035fe97) X GRANT
96 9 1769773362 0 PAG 1:138823 IX GRANT
96 9 1769773362 11 KEY (be02b41814a8) X GRANT
96 9 1769773362 7 KEY (8901450658e7) X GRANT
96 9 1769773362 0 PAG 1:207983 IX GRANT
96 9 1769773362 0 PAG 1:192408 IX GRANT
96 9 1769773362 0 RID 1:201756:4 X GRANT
96 9 1769773362 7 KEY (2d02f93e1fa8) X GRANT
96 9 1769773362 5 KEY (2e0180c4f5cc) X GRANT
96 9 1769773362 13 KEY (0e0176e75ab0) X GRANT
96 9 1769773362 10 KEY (5202b3d2d16f) X GRANT
96 9 1769773362 12 KEY (8e02fcf4942b) X GRANT
96 9 1769773362 6 KEY (4b0234be7a46) X GRANT
96 9 1769773362 9 KEY (4f013a48a5aa) X GRANT
96 9 1769773362 5 KEY (0701395ba1fa) X GRANT
96 9 1769773362 0 PAG 1:228571 IX GRANT
96 9 1769773362 0 PAG 1:138822 IX GRANT
96 9 1769773362 0 RID 1:138817:16 X GRANT
96 9 1769773362 0 PAG 1:113121 IX GRANT
96 9 1769773362 9 KEY (7801aa92f8c1) X GRANT
96 9 1769773362 6 KEY (4402ed92c5a6) X GRANT
96 9 1769773362 0 PAG 1:207982 IX GRANT
96 9 1769773362 3 KEY (b90186b6a845) X GRANT
96 9 1769773362 0 RID 1:201757:4 X GRANT
96 9 1769773362 7 KEY (8500b01c3c26) X GRANT
96 9 1769773362 3 KEY (f4013a3fc39f) X GRANT
96 9 1769773362 6 KEY (17037f301d29) X GRANT
96 9 1769773362 12 KEY (01027d2e7328) X GRANT
96 9 1769773362 11 KEY (7b013bc5a47c) X GRANT
96 9 1769773362 0 PAG 1:196541 IX GRANT
96 9 1769773362 7 KEY (56028f6217a5) X GRANT
96 9 1769773362 9 KEY (8401db25d8b3) X GRANT
96 9 1769773362 4 KEY (c302798b255a) X GRANT
96 9 1769773362 9 KEY (4202dad1c1cf) X GRANT
96 9 1769773362 11 KEY (f1019de4a718) X GRANT
96 9 1769773362 11 KEY (ac01344c58f2) X GRANT
96 9 1769773362 0 RID 1:138822:16 X GRANT
96 9 1769773362 0 PAG 1:138817 IX GRANT
96 9 1769773362 13 KEY (6801a9dac6b8) X GRANT
96 9 1769773362 0 PAG 1:192414 IX GRANT

"Paul Ibison" <anon...@discussions.microsoft.com> wrote in message
news:e07201c3f0af$2de91960$a301...@phx.gbl...

Paul Ibison

unread,

Feb 11, 2004, 10:38:00 AM2/11/04

to

Alex - did you restart the agents after adding these
parameters into the job's step, and remove all users
before synchronising?
Regards,
Paul

>.
>

Alex B

unread,

Feb 11, 2004, 10:41:50 AM2/11/04

to

Yes...

Thanks for your interest! This has me totally, completely befuddled... a
20k one field update should replicate in no time, I'd think...

"Paul Ibison" <anon...@discussions.microsoft.com> wrote in message

news:ec0301c3f0b5$07bd5020$a501...@phx.gbl...

Paul Ibison

unread,

Feb 11, 2004, 10:49:26 AM2/11/04

to

Me too! Perhaps use profiler on the subscriber and see if
it is still going across as a transaction rather than
individual commands.
Reagrds,
Paul

GKramer

unread,

Feb 12, 2004, 2:56:06 AM2/12/04

to

Hi

As a newbee to SQLserver but with some experiance on other RDBMSes ( see 3 lines down in this forum ) I can suggest to split up these two actions -- set up two different publications and schedule them 3-4 minuter after eachother ( regarding the dataset and speed of your system )

GKramer

Shirley Kelly [MS]

unread,

Feb 15, 2004, 5:32:01 AM2/15/04

to

You may see how many rows are in the msrepl_transactions and
msrepl_commands tables in the distribution database. Use COUNT(*) in TSQL
to do this. You may find that there are many more than 23,000 transactions
waiting to be distributed.

You could also configure an output file for the distribution agent to see
if transactions are actually being delivered. KB article 312292
(http://support.microsoft.com/?id=312292) explains how to do this.

Regards,
Shirley
SQL Server Support