SQL Server Performance

SQL Transactional Replication Issue

Discussion in 'Performance Tuning for SQL Server Replication' started by vivianlly, Aug 29, 2003.

  1. vivianlly New Member

    I set up transactional replication on SQL 2K database. Some of the tables are replicated using store procedures while others are replicated using sql commands.

    Some of these tables also contains triggers. However, these triggers are coded with syntax "NOT FOR REPLICATION"

    All manipulation of data are done using ado transaction via the application.

    My problem is I get Error Executing a batch, Execution of individual
    commands succeeded. And then it just stops after retrying for many times.
    It causes my replication to slow down whenever that happens. Then when error occurs I have to manually starts it or skip the error.

    I am wondering what could cause sql to execute individual commands /executing batch AND HOW TO PREVENT/SOLVE this problem.

    Your thoughts and help will be much appreciated.
  2. satya Moderator

    What was the exact error and service pack level on SQL?

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  3. vivianlly New Member

    There is no exact error except it just kept saying
    "Execution of Individual Commands"

    and it will retries many times if it hasn't succeed until either it gets tired of trying or it succeeded. When succeeded then it will says

    "Execution of Individual Commands Succeeded." else it just gives an error that says
    something about not wanting to retry anymore (sorry I forget to log down the exact error but I am pretty sure it's about tired of retrying)
  4. vivianlly New Member

    Ok I manage to capture the exact error frm the Action Message that appears in a sequence:

    - Execution of individual commands succeeded. NOTE: The step was retried the requested
    number of times (10) without succeeding. The step failed.

    - Execution of individual comamnds succeeded.

    - Error executing a batch of commands. Retrying individual commands.
  5. satya Moderator

    I don't see anything to suspect, recheck whether the replication process is working overall between the servers.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  6. vivianlly New Member

    As mentioned in my previous posting, I uses script for my replication.

    One of the script which I used is of Text datatype where the data is in XML format.

    So when I ran sp_browsereplcmds to view the process in replication, I found the xml data seems to be corrupted because somewhere in the data it appends with some funny binary numbers e.g.

    <Remarks>Test</Remarks>
    <IsChanged>fals
    0x0000021E000005A20001 0 2 6 0 e</IsChanged>
    <Reason />

    Note the <IsChanged> data -> it seems to be corrupted. Every time when I run the same application, it gives me this sort of error and SQL will keep on retrying to execute invidual commands.

    Any idea why and how I can solve this problem?
  7. vivianlly New Member

    sorry a few mistakes in my previous post.
    I meant one of the paramenter used in the replication script is of Text datatype where the data is XML data.

    the text data is appended not with binary but with hex # (i think)
    e.g.

    <Remarks>Test</Remarks>
    <IsChanged>fals
    0x0000021E000005A20001 0 2 6 0 e</IsChanged>
    <Reason />

  8. satya Moderator

    May track the activity using PROFILER.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  9. vivianlly New Member

    Ok I managed to put the replication output into a file and found that the parameter of text datatype used to store xml data which got replicated was indeed the problem.

    Any idea how i can resolve this?

    Here is how it looks like and I wonder why .. coz it only happens to sometimes and not all the time even though the same application was submitted.


    37000 Line 1: Incorrect syntax near '2004-01-05 16:05:11.710'. 170

    Failed command = {CALL sp_ReplicateApplication (350, 2, 'AA Anthony & Co. Sdn Bhd ', 0, NULL, 0, 0, NULL, NULL)} {CALL sp_ReplicateRevision (0, 350, 4, 0, 'ant', 'Anthony', 'aifong@ersela.com.my', {ts '2004-01-05 16:05:11.710'}, 'ant', 'Anthony', {ts '2004-01-05 16:05:11.710'}, NULL, NULL, NULL, {lc '#16188;#28024;#8300;#25974;#29554;#28521;#15726;#12578;#12334;•#28261;#28515;#26980;#26478;#8765;#29813;#11622;#13873;#16162;#3390;#15370;#28483;#29301;#25971;#28737;#27760;#25449;#29793;#28521;#8302;#28024;#28268;#14963;#29560;#15716;#26658;#29812;#14960;#12079;#30583;#11895;#13175;#28462;#26482;#12847;#12336;#12081;#19800;#21324;#26723;#28005;#8801;#30752;#27757;#29550;#30778;#26995;#8765;#29800;#28788;#12090;#30511;#30583;#30510;#11827;#29295;#12135;#12338;#12592;#22575;#19533;#25427;#25960;#24941;#26925;#29550;#24948;#25454;#8805;#3390;#8202;#15392;#28737;#27760;#25449;#29793;#28521;#18798;#15940;#13619;#15408;#16687;#28784;#26988;#24931;#26996;#28271;#17481;#3390;#8202;#15392;#28737;#27760;#25449;#29793;#28521;#21614;#28793;#15973;#28483;#29301;#25971;#25934;#15479;#16687;#28784;#26988;#24931;#26996;#28271;#31060;#25968;#3390;#8202;#15392;#29779;#29793;#29557;#20542;#28261;#26980;#26478;#12092;#29779;#29793;#29557;#3390;#8202;#15392;#25938;#29537;#28271;#21566;#29541;#15476;#21039;#24933;#28531;#15982;#2573;†#21052;#30309;#29545;#28521;#18798;#15940;#15408;#21039;#30309;#29545;#28521;#18798;#15940;#2573;†#19516;#29793;#29541;#21108;#30309;#29545;#28521;#18798;#15940;#15408;#19503;#29793;#29541;#21108;#30309;#29545;#28521;#18798;#15940;#2573;†#17468;#21570;#24946;#28265;#28265;#20583;#28530;#26998;#25956;#18802;#15940;#15410;#17455;#21570;#24946;#28265;#28265;#20583;#28530;#26998;#25956;#18802;#15940;#2573;†#17468;#21570;#24946;#28265;#28265;#20583;#28530;#26998;#25956;#20082;#28001;#15973;#16705;#16672;#29806;#28520;#31086;#9760;#28001;#15216;#17184;#11887;#21280;#28260;#16928;#25704;#15392;#17455;#21570;#24946;#28265;#28265;#20583;#28530;#26998;#25956;#20082;#28001;#15973;#2573;†#17468;#20546;#25970;#26998;#30063;#21107;#30309;#29545;#28521;#18798;#15940;#15408;#17455;#20546;#25970;#26998;#30063;#21107;#30309;#29545;#28521;#18798;#15940;#2573;†#17468;#20546;#29281;#28261;#16756;#28784;#26988;#24931;#26996;#28271;#17481;#12350;#12092;#16964;#24912;#25970;#29806;#28737;#27760;#25449;#29793;#28521;#18798;#15940;#2573;†#17468;#21314;#25205;#26989;#29812;#25701;#31042;#29525;#29285;#17481;#24894;#29806;#12092;#16964;#30035;#28002;#29801;#25972;#16996;#21881;#25971;#18802;#15940;#2573;†#17468;#21314;#25205;#26989;#29812;#25701;#31042;#29525;#29285;#24910;#25965;#16702;#29806;#28520;#31086;#12092;#16964;#30035;#28002;#29801;#25972;#16996;#21881;#25971;#20082;#28001;#15973;#2573;†#17468;#21314;#25205;#26989;#29812;#25701;#31042;#27973;#26977;#16748;#25700;#25970;#29555;#24894;#26217;#28271;#16487;#29285;#25971;#24940;#25390;#28015;#27950;#15481;#17455;#21314;#25205;#26989;#29812;#25701;#31042;#27973;#26977;#16748;#25700;#25970;#29555;#3390;#8202;#15392;#16964;#30035;#28002;#29801;#25972;#17508;#29793;#21605;#28009;#15973;#12338;#13360;#12333;#11569;#13616;#12628;#14902;#13616;#12602;#11825;#12599;#14640;#14131;#11061;#14384;#12346;#15408;#17455;#21314;#25205;#26989;#29812;#25701;#24900;#25972;#26964;#25965;#3390;#8202;#15392;#16964;#24908;#29811;#26691;#28257;#25959;#16996;#21881;#25971;#18802;#15940;#28257;#15476;#17455;#19522;#29537;#17268;#24936;#26478;#25701;#31042;#29525;#29285;#17481;#3390;#8202;#15392;#16964;#24908;#29811;#26691;#28257;#25959;#16996;#21881;#25971;#20082;#28001;#15973;#28225;#26740;#28271;#15481;#17455;#19522;#29537;#17268;#24936;#26478;#25701;#31042;#29525;#29285;#24910;#25965;#3390;#8202;#15392;#16964;#24908;#29811;#26691;#28257;#25959;#21860;#25971;#17522;#29793;#15973;#12338;#13360;#12333;#11569;#13616;#12628;#14902;#13616;#12602;#11825;#12599;#14640;#14131;#11061;#14384;#12346;#15408;#17455;#19522;#29537;#17268;#24936;#26478;#25701;#29525;#29285;#24900;#25972;#3390;#8202;#15392;#16964;#24908;#29811;#26691;#28257;#25959;#16996;#21369;#25445;#25970;#24948;#26994;#29793;#17481;#12350;#12092;#16964;#24908;#29811;#26691;#28257;#25959;#16996;#21369;#25445;#25970;#24948;#26994;#29793;#17481;#3390;#8202;#15392;#16964;#24908;#29811;#26691;#28257;#25959;#16996;#21369;#25445;#25970;#24948;#26994;#29793;#24910;#25965;#12064;#3390;#8202;#15392;#16964;#24908;#29811;#26691;#28257;#25959;#16996;#21369;#25445;#25970;#24948;#26994;#29793;#24900;#25972;#26964;#25965;#12350;#12336;#11569;#12592;#12333;#21553;#12336;#12346;#14896;#12336;#12334;#12336;#12336;#12336;#12331;#14904;#12336;#12092;#16964;#24908;#29811;#26691;#28257;#25959;#16996;#21369;#25445;#25970;#24948;#26994;#29793;#24900;#25972;#26964;#25965;#3390;#8202;#15392;#16964;#29513;#25938;#27760;#25449;#29793;#25701;#12350;#12092;#16964;#29513;#25938;#27760;#25449;#29793;#25701;#3390;#8202;#15392;#24908;#29811;#28737;#29296;#30319;#25701;#24900;#25972;#12350;#12336;#11569;#12592;#12333;#21553;#12336;#12346;#14896;#12336;#12334;#12336;#12336;#12336;#12331;#14904;#12336;#12092;#24908;#29811;#28737;#29296;#30319;#25701;#24900;#25972;#3390;#8202;#15392;#24898;#25971;#28483;#29301;#25971;#17481;#12350;#12092;#24898;#25971;#28483;#29301;#25971;#17481;#3390;#8202;#15392;#28486;#28018;#3390;#8202;†#15392;#28483;#29301;#25971;#25924;#24948;#27753;#3390;#8202;††#15392;#28483;#29301;#25971;#17481;#12350;#12092;#28483;#29301;#25971;#17481;#3390;#8202;††#15392;#20547;#20549;#26991;#29806;#15987;#15408;#17199;#17744;#28496;#28265;#29556;#3390;#8202;††#15392;#28483;#29301;#25971;#25924;#26988;#25974;#31090;#25933;#26980;#28021;#17214;#21060;#19791;#28483;#29301;#25971;#12092;#28483;#29301;#25971;#25924;#26988;#25974;#31090;#25933;#26980;#28021;#3390;#8202;††#15392;#28483;#29301;#25971;#29779;#29793;#29557;#17726;#28781;#31092;#12092;#28483;#29301;#25971;#29779;#29793;#29557;#3390;#8202;††#15392;#24910;#25965;#21566;#29541;#8308;#25938;#26998;#26995;#28271;#17481;#12832;#11552;#21024;#30063;#25710;#12832;#12092;#24910;#25965;#3390;#8202;††#15392;#29779;#29281;#17524;#29793;#15973;#14641;#12336;#12333;#11569;#12592;#12372;#14896;#12336;#12346;#11824;#12336;#12336;#12336;#11056;#14384;#12346;#15408;#21295;#24948;#29810;#24900;#25972;#3390;#8202;††#15392;#28229;#17508;#29793;#15973;#14641;#12336;#12333;#11569;#12592;#12372;#14896;#12336;#12346;#11824;#12336;#12336;#12336;#11056;#14384;#12346;#15408;#17711;#25710;#24900;#25972;#3390;#8202;††#15392;#28492;#24931;#26996;#28271;#17481;#12350;#12092;#28492;#24931;#26996;#28271;#17481;#3390;#8202;††#15392;#25167;#25962;#29795;#30313;#29541;#21566;#29541;#15476;#20271;#27234;#25445;#26996;#25974;#15987;#2573;†††#17212;#30063;#29554;#19557;#30309;#27749;#21087;#26213;#25970;#26739;#29285;#29758;#30066;#15461;#17199;#30063;#29554;#19557;#30309;#27749;#21087;#26213;#25970;#26739;#29285;#3390;#8202;††#15392;#28483;#29301;#25971;#25932;#25974;#24428;#28233;#25972;#28018;#25701;#24937;#25972;#26174;#27745;#25971;#12092;#28483;#29301;#25971;#25932;#25974;#24428;#28233;#25972;#28018;#25701;#24937;#25972;#3390;#8202;††#15392;#28483;#29301;#25971;#25932;#25974;#24428;#25665;#24950;#25454;#25701;#26174;#27745;#25971;#12092;#28483;#29301;#25971;#25932;#25974;#24428;#25665;#24950;#25454;#25701;#3390;#8202;††#15392;#24916;#26482;#29797;#30017;#26980;#28261;#25955;#21566;#29541;#15476;#21551;#29281;#25959;#16756;#25717;#25961;#25454;#15973;#2573;†††#19004;#26991;#29806;#25942;#29806;#29301;#15973;#24934;#29548;#15461;#18991;#26991;#29806;#25942;#29806;#29301;#15973;#2573;†††#17212;#30063;#29554;#21605;#28793;#24421;#28233;#28488;#29557;#15973;#29300;#25973;#12092;#28483;#29301;#25971;#31060;#25968;#18783;#18542;#30063;#25971;#3390;#8202;††#15392;#28483;#29301;#25971;#31060;#25968;#20575;#25205;#26988;#15971;#24934;#29548;#15461;#17199;#30063;#29554;#21605;#28793;#24421;#30032;#27746;#25449;#3390;#8202;††#15392;#25938;#24941;#27506;#15987;#25940;#29811;#12092;#25938;#24941;#27506;#15987;#2573;†††#18748;#17267;#24936;#26478;#25701;#26174;#27745;#25971;#12092;#29513;#26691;#28257;#25959;#15972;#2573;†††#21052;#24933;#28531;#8302;#15919;#2573;†††#21308;#29557;#25968;#29550;#28521;#21358;#24948;#29810;#24900;#25972;#12606;#12345;#11568;#12592;#12333;#21553;#12336;#12346;#14896;#12336;#12334;#12336;#12336;#12336;#12331;#14904;#12336;#12092;#30035;#28787;#28261;#26995;#28271;#29779;#29281;#17524;#29793;#15973;#2573;†††#21308;#29557;#25968;#29550;#28521;#17774;#25710;#24900;#25972;#12606;#12345;#11568;#12592;#12333;#21553;#12336;#12346;#14896;#12336;#12334;#12336;#12336;#12336;#12331;#14904;#12336;#12092;#30035;#28787;#28261;#26995;#28271;#28229;#17508;#29793;#15973;#2573;††#12092;#28483;#29301;#25971;#25924;#24948;#27753;#3390;#8202;†#15392;#28483;#29301;#25971;#25926;#29541;#3390;#8202;††#15392;#29513;#26691;#28257;#25959;#15972;#24934;#29548;#15461;#18735;#17267;#24936;#26478;#25701;#3390;#8202;††#15392;#25926;#29541;#3390;#8202;†††#15392;#25926;#15973;#2573;†††††#20540;#26994;#25955;#13374;#12336;#12092;#29264;#25449;#15973;#2573;†††††#17212;#29793;#26469;#29295;#8313;#15919;#2573;††††#12092;#25926;#15973;#2573;†††#12092;#25926;#29541;#3390;#8202;†#15392;#17199;#30063;#29554;#18021;#25957;#15987;#2573;††#17212;#30063;#29554;#17253;#28271;#24948;#29795;#25924;#24948;#27753;#15987;#2573;†††#17212;#28271;#24948;#29795;#25936;#29554;#28271;#15987;#2573;††††#17212;#28271;#24948;#29795;#25936;#29554;#28271;#21566;#29541;#15476;#17199;#28271;#24948;#29795;#25936;#29554;#28271;#3390;#8202;††#15392;#17199;#28271;#24948;#29795;#25936;#29554;#28271;#15987;#2573;†††#16700;#25700;#25970;#29555;#21566;#29541;#15476;#16687;#25700;#25970;#29555;#3390;#8202;††#15392;#26704;#28271;#20069;#28021;#25954;#15986;#13108;#13108;#13108;#12092;#26704;#28271;#20069;#28021;#25954;#15986;#2573;†††#17980;#30817;#30030;#25197;#29285;#13374;#13363;#13363;#12092;#24902;#20088;#28021;#25954;#15986;#2573;†††#17724;#24941;#27753;#21566;#29509;#16500;#25972;#29811;#25390;#28015;#12092;#27973;#26977;#15980;#2573;†††#22332;#25189;#26995;#25972;#12064;#3390;#8202;††#15392;#29513;#26691;#28257;#25959;#15972;#24934;#29548;#15461;#18735;#17267;#24936;#26478;#25701;#3390;#8202;†#15392;#17199;#30063;#29554;#17253;#28271;#24948;#29795;#25924;#24948;#27753;#15987;#2573;††#17212;#30063;#29554;#21349;#25968;#27489;#29285;#15987;#2573;†††#18748;#17267;#24936;#26478;#25701;#26174;#27745;#25971;#12092;#29513;#26691;#28257;#25959;#15972;#2573;†††#21308;#25968;#27489;#29285;#15987;#2573;††††#21308;#25968;#27489;#29285;#3390;#8202;††††#15392;#28755;#24933;#25963;#18802;#15940;#15408;#21295;#25968;#27489;#29285;#17481;#3390;#8202;††††#15392;#21070;#17225;#3390;#8202;†††††#15392;#25939;#29795;#28521;#12654;#12606;#12593;#12593;#15409;#21295;#25445;#26996;#28271;#15921;#2573;††††††#21308;#25445;#26996;#28271;#15922;#12593;#12092;#25939;#29795;#28521;#12910;#3390;#8202;†††††#15392;#25939;#29795;#28521;#13166;#12606;#12593;#15409;#21295;#25445;#26996;#28271;#15923;#2573;††††††#17980;#27765;#18796;#15939;#12593;#12593;#12593;#12593;#12593;#12593;#12092;#30022;#27756;#17225;#3390;#8202;††††#15392;#20015;#18770;#15939;#2573;†††††#21308;#27745;#29813;#29793;#28521;#15982;#25940;#29811;#12092;#24915;#30060;#24948;#26996;#28271;#3390;#8202;††††#15392;#24910;#25965;#21566;#29541;#15476;#20015;#28001;#15973;#2573;†††††#21820;#27760;#24943;#18020;#27753;#15973;#2573;††††††#21820;#27760;#24943;#18788;#15940;#15408;#21807;#27760;#24943;#18788;#15940;#2573;††††††#17468;#25455;#26956;#17250;#29793;#27745;#26479;#17481;#12350;#12092;#28484;#19555;#25193;#24899;#24948;#28524;#18791;#15940;#2573;††††††#21820;#27760;#24943;#20580;#29793;#15976;#12338;#13360;#13617;#13873;#13109;#12092;#28757;#28524;#25697;#24912;#26740;#3390;#8202;†††††#15392;#26950;#25964;#24910;#25965;#29758;#29541;#11892;#30836;#15476;#17967;#27753;#20069;#28001;#15973;#2573;††††††#19516;#29537;#19828;#25711;#26217;#25961;#15972;#12336;#12592;#12333;#11569;#12592;#12372;#14896;#12336;#12346;#11824;#12336;#12336;#12336;#11056;#14384;#12346;#15408;#19503;#29537;#19828;#25711;#26217;#25961;#15972;#2573;††††††#18748;#17267;#24936;#26478;#25701;#29758;#30066;#15461;#18735;#17267;#24936;#26478;#25701;#3390;#8202;†††††#15392;#24898;#25971;#28757;#28524;#25697;#24912;#26740;#23614;#25948;#29554;#30253;#30313;#24937;#23662;#28789;#28524;#25697;#15452;#16943;#29537;#21861;#27760;#24943;#20580;#29793;#15976;#2573;†††††#12092;#28757;#28524;#25697;#26950;#25964;#3390;#8202;††††#15392;#24914;#26996;#26478;#12350;#12092;#24914;#26996;#26478;#3390;#8202;††††#15392;#20547;#20549;#26991;#29806;#15987;#15408;#17199;#17744;#28496;#28265;#29556;#3390;#8202;††††#15392;#21321;#25936;#29554;#28271;#17481;#12350;#12092;#21321;#25936;#29554;#28271;#17481;#3390;#8202;††††#15392;#21321;#24908;#29811;#26691;#28257;#25959;#17508;#29793;#21605;#28009;#15973;#12336;#12592;#12333;#11569;#12592;#12372;#14896;#12336;#12346;#11824;#12336;#12336;#12336;#11056;#14384;#12346;#15408;#18735;#19539;#29537;#17268;#24936;#26478;#25701;#24900;#25972;#26964;#25965;#3390;#8202;††††#15392;#25936;#29554;#28271;#17481;#12350;#12092;#25936;#29554;#28271;#17481;#3390;#8202;††††#15392;#24908;#29811;#26691;#28257;#25959;#17508;#29793;#21605;#28009;#15973;#12336;#12592;#12333;#11569;#12592;#12372;#14896;#12336;#12346;#11824;#12336;#12336;#12336;#11056;#14384;#12346;#15408;#19503;#29537;#17268;#24936;#26478;#25701;#24900;#25972;#26964;#25965;#3390;#8202;††††#15392;#26956;#25955;#25454;#20069;#28021;#25954;#29554;#12064;#3390;#8202;†††#15392;#21295;#25968;#27489;#29285;#3390;#8202;††#15392;#21295;#25968;#27489;#29285;#15987;#2573;††#12092;#28483;#29301;#25971;#28755;#24933;#25963;#29554;#3390;#8202;†#15392;#17475;#20306;#20301;#29813;#26988;#25966;#3390;#8202;††#15392;#29513;#26691;#28257;#25959;#15972;#24934;#29548;#15461;#18735;#17267;#24936;#26478;#25701;#3390;#8202;††#15392;#28500;#26992;#29539;#3390;#8202;†††#15392;#28500;#26992;#15971;#2573;†††††#20028;#28001;#15973;#25940;#29811;#12092;#24910;#25965;#3390;#8202;††††#15392;#25924;#25459;#26994;#29808;#28521;#15982;#25972;#29556;#15461;#17455;#29541;#29283;#28777;#26996;#28271;#3390;#8202;††††#15392;#28483;#29301;#25971;#30031;#27764;#28265;#21605;#28783;#25449;#17481;#12064;#3390;#8202;††††#15392;#28755;#24933;#25963;#29554;#3390;#8202;†††††#15392;#28755;#24933;#25963;#15986;#2573;†††††††#21308;#25968;#27489;#29285;#17481;#12350;#12092;#28755;#24933;#25963;#18802;#15940;#2573;†††††††#20028;#18770;#15939;#2573;††††††††#21308;#25445;#26996;#28271;#15921;#12593;#12593;#12593;#12092;#25939;#29795;#28521;#12654;#3390;#8202;†††††††#15392;#25939;#29795;#28521;#12910;#12606;#15409;#21295;#25445;#26996;#28271;#15922;#2573;††††††††#21308;#25445;#26996;#28271;#15923;#12593;#12593;#12092;#25939;#29795;#28521;#13166;#3390;#8202;†††††††#15392;#30022;#27756;#17225;#12606;#12593;#12593;#12593;#12593;#12593;#15409;#17967;#27765;#18796;#15939;#2573;†††††††#12092;#21070;#17225;#3390;#8202;††††††#15392;#24915;#30060;#24948;#26996;#28271;#21566;#29541;#15476;#21295;#27745;#29813;#29793;#28521;#15982;#2573;†††††††#20028;#28001;#15973;#25940;#29811;#12092;#24910;#25965;#3390;#8202;††††††#15392;#28757;#28524;#25697;#26950;#25964;#3390;#8202;†††††††#15392;#28757;#28524;#25697;#17481;#12350;#12092;#28757;#28524;#25697;#17481;#3390;#8202;†††††††#15392;#28484;#19555;#25193;#24899;#24948;#28524;#18791;#15940;#15408;#17455;#25455;#26956;#17250;#29793;#27745;#26479;#17481;#3390;#8202;†††††††#15392;#28757;#28524;#25697;#24912;#26740;#12862;#12336;#12596;#12597;#13622;#15411;#21807;#27760;#24943;#20580;#29793;#15976;#2573;††††††††#17980;#27753;#20069;#28001;#15973;#25972;#29811;#29742;#29816;#12092;#26950;#25964;#24910;#25965;#3390;#8202;†††††††#15392;#24908;#29811;#28493;#26980;#26982;#25701;#12350;#12336;#11569;#12592;#12333;#21553;#12336;#12346;#14896;#12336;#12334;#12336;#12336;#12336;#12331;#14904;#12336;#12092;#24908;#29811;#28493;#26980;#26982;#25701;#3390;#8202;†††††††#15392;#29513;#26691;#28257;#25959;#15972;#29300;#25973;#12092;#29513;#26691;#28257;#25959;#15972;#2573;††††††††#16956;#29537;#21861;#27760;#24943;#20580;#29793;#15976;#23644;#29285;#11635;#26998;#26998;#28257;#30044;#27760;#24943;#23652;#12092;#24898;#25971;#28757;#28524;#25697;#24912;#26740;#3390;#8202;††††††#15392;#21807;#27760;#24943;#18020;#27753;#15973;#2573;†††††††#21052;#29793;#28265;#15975;#15408;#21039;#29793;#28265;#15975;#2573;†††††††#17212;#17744;#28496;#28265;#29556;#12350;#12092;#20547;#20549;#26991;#29806;#15987;#2573;†††††††#18748;#20563;#29285;#28531;#18798;#15940;#15408;#18735;#20563;#29285;#28531;#18798;#15940;#2573;†††††††#18748;#19539;#29537;#17268;#24936;#26478;#25701;#24900;#25972;#26964;#25965;#12350;#12336;#11569;#12592;#12333;#21553;#12336;#12346;#14896;#12336;#12334;#12336;#12336;#12336;#12331;#14904;#12336;#12092;#21321;#24908;#29811;#26691;#28257;#25959;#17508;#29793;#21605;#28009;#15973;#2573;†††††††#20540;#29285;#28531;#18798;#15940;#15408;#20527;#29285;#28531;#18798;#15940;#2573;†††††††#19516;#29537;#17268;#24936;#26478;#25701;#24900;#25972;#26964;#25965;#12350;#12336;#11569;#12592;#12333;#21553;#12336;#12346;#14896;#12336;#12334;#12336;#12336;#12336;#12331;#14904;#12336;#12092;#24908;#29811;#26691;#28257;#25959;#17508;#29793;#21605;#28009;#15973;#2573;†††††††#19516;#25449;#28261;#25955;#30030;#25197;#29285;#8307;#15919;#2573;††††††#12092;#28755;#24933;#25963;#15986;#2573;†††††#12092;#28755;#24933;#25963;#29554;#3390;#8202;†††#15392;#21551;#28783;#25449;#3390;#8202;††#15392;#21551;#28783;#25449;#15987;#2573;††#12092;#17475;#20306;#20301;#29813;#26988;#25966;#3390;#8202;#15392;#17967;#29295;#15981;#2573;†#16700;#29795;#28521;#18798;#15940;#27973;#29808;#15481;#16687;#29795;#28521;#18798;#15940;#2573;#12092;#28483;#29301;#25971;#28737;#27760;#25449;#29793;#28521;#15982;
    Last transaction timestamp: 0x0000001400000eb9000100000000
    Transaction seqno: 0x0000001400000eb90001



  10. Luis Martin Moderator

    Are all soft involucrate with the same date format?

    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  11. satya Moderator

    Any information from PROFILER trace during this activity?

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  12. vivianlly New Member

    Everytime it happens the profiler application name - Replication Distribution History and will appear with text data usually looks something like :

    exec sp_MSadd_distribution_history 3, 3, N'1 transaction(s)...
    -- sp_MSrepl_raiserror if @status = 2 -- Succeeded
    -- sp_Msrepl_raiserror if @status = 5 -- Retry failure ...

    The text data is always appended with funny characters when it happens .. its looks like some coded data like ÄϾ©ÖÐɽֲÄϾ©ÖÐɽֲÎïÔ°ÎïÔ° .. ydayter in my post when I C&P directly frm the output file it turns out #numbers instead of the ÄϾ©ÖÐɽֲÎïÔ° after it get posted.
  13. satya Moderator

    What are collation settings on all the databases?
    And also service pack level?

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  14. vivianlly New Member

    Version 5 (Build 2195: Service Pack 3)
    Collation for text and varchar are all set to -> SQL_Latin1_General_CP1_CI_AS
  15. vivianlly New Member

    Sorry that was the console version. My sql version is 8.00.760 (SP3)
  16. satya Moderator

  17. vivianlly New Member

    This is a very weird problem. I found out that it only happens to my development and test environment but when it is ported to the production environment it works fine without retry error.

    I suspect it could be either due to network connection/bandwidth or some settings in the sql server which triggers this problem.

    Anyway, thanks for your time and help.
  18. satya Moderator

    Ensure both the environments are similar and patched upto the level/.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  19. cjanner New Member

    I have SQL Server 2000 and ArcSde 8.3 running together on one server using transactional
    replication from one server to a read-only one.

    After I edited the publication of the SDE database and reinitialized both subscriptions
    for that publication, I get the following error:

    Cannot alter table 'SDE_geometry_columns' because this table does not exist in database 'SDE'.
    (Source: DEVGISSQL (Data source); Error number: 4902)
    ------------------------------------------------------------------------------------------------------

    However, the table does indeed exist on the publication database and both the subscribers
    with the same time and date stamp.

    Any ideas what caused this problem or how to get around it?
  20. SQLJoe New Member

    It is likely you have an open transaction due to the batch process failing without rolling back. When you see this error check if you get an open transaction by typing "dbcc opentran". If so, kill the batch process within SQL (obtained by sp_who2 or select * from master..sysprocesses).

    In addition to your replication problem, you should see a large amount of block issues. In summary, it is the failed batch process that is stalling your SQL server and the replication processes. So kill the batch process whenever it is open and stalling. The permanent solution, of course, is to fix the batch process.

Share This Page