Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2003
    Location
    cape town, south africa
    Posts
    102

    Unanswered: Blocking repicated deletes

    Hi

    I keep data in four seperate locations, each with different data retention.
    the problem is, i keep the publisher "thin" - only 24 hrs of data) the data in the relevant tables is about 250000 a day) and these deletes are blocked at the subscribers by commenting out the body of the repl proc.
    this does however cause a burst in bandwith & locks the subscribers, even though no actual delete takes place.
    does anyone know if you can actuaklly NOT REPLICATE delete commands for specified tables at all - so that the message of the delete wont even be SENT to the subscriber?

    Des

  2. #2
    Join Date
    May 2003
    Location
    UK
    Posts
    220
    I'm not an expert, but can you schedule the delete for late at night when its not an issue?

  3. #3
    Join Date
    Jun 2003
    Location
    cape town, south africa
    Posts
    102
    Unfortunately not an option - you see, the operations are at their peak (USA) when it is the middle of the night here (SA) - and then when it is calm there, all the reporting/work is being done here - so there is no downtime so to speak - it isnt a killer, but it would definitly be cool if i didnt have to replicate the delete commmands at all...
    thx

  4. #4
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    No joy here Desmond. It is the entries in the transaction log that are sent to the distributor which are then forwarded to the subscriber(s) to be implemented via the replication sprocs (congrats on finding those by the way ).

    You would either have to hack the transaction log at the publisher to remove them (which would invalidate any backups you have) or find a way at the distributor to remove them.

    Bear in mind also, that when a column is updated that participates in an index, it is actually performed with a delete and insert, instead of an update in place. I discovered this with Log Explorer a couple of years back.

  5. #5
    Join Date
    Jun 2003
    Location
    cape town, south africa
    Posts
    102
    thanks tomh,
    that last bit explains some trouble i had with updates on one of the tables whose delete proc was nulled - the symptoms pointed that way, but i couldnt say for sure..

    cheers
    des
    ps. i dont think i am going to hack the logs - i will just learn to live with the problem :-)

  6. #6
    Join Date
    Jun 2003
    Location
    cape town, south africa
    Posts
    102

    there is a way to block deletes

    The answer to avoiding replicating huge batches of deletes is this:
    publish the proc that does the deletes, specify to repicate its execution,
    make the one on the subscriber just return 0.
    then exec th oine on the publisher - regardless of how many rows it affects on the publisher, only ONE command ets sent to the subscriber - the exec.
    huge problem solved...
    :-)

  7. #7
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Nice work! I'll put that one in my toolbox in case I ever need it!!


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •