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?

DesI'm not an expert, but can you schedule the delete for late at night when its not an issue?|||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|||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.|||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 :-)|||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...
:-)|||Nice work! I'll put that one in my toolbox in case I ever need it!!

:cool:

Blocking

I am doing an insert into a table which is taking forever
2 hrs and still waiting for 20 million records
But while its doing the insert i am able to do
select count(*) from table and its giving me 0
my question how cum select is not blocked by above insert
sanjaySanjay,
You might read some under this topic:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_7a_6fhj.asp
And this topic:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_sa2_2sit.asp
SQL Server default is to use the isolation level "Read committed". This
means that it will read the rows already there, but none of those in the
insert transaction.
By the way, you are apparently inserting 20 million records in a single
transaction. This will take much longer and use way more log space than
inserting them in smaller chunks. FWIW, I usually keep the insert blocks
down to about 10,000 rows, but that is not a scientific number. :)
Russell Fields
"Sanjay" <sanjayg@.hotmail.com> wrote in message
news:03a501c34578$ce926ac0$a101280a@.phx.gbl...
> I am doing an insert into a table which is taking forever
> 2 hrs and still waiting for 20 million records
> But while its doing the insert i am able to do
> select count(*) from table and its giving me 0
> my question how cum select is not blocked by above insert
> sanjay