sql server replication: Large delete stmt sent thru Distr. Agent -- sql server replication

Large delete stmt sent thru Distr. Agent

Chris
9/19/2005 3:58:01 PM

sql server replication:

I have a nightly routine that deletes 270K rows from a replicated table via
transactional replication. My distr agent is using the built-in Verbose
profile, -CommitBatchSize set to 100

Subscriber is connected via T1 line in building across street, I stopped my
distr agent after 100K rows were deleted which took about 4 hours.
Unfortunately it appears all delete stmts were rolled back, table is in it's
original 270K row count state when the distr agent came to a stop. The Last
Action was displaying "Delivering replicated transactions". Ran profiler at
subscriber and found sp_MSdel_tablename sprocs executing for each row...

Why would the deletes not be committed after 100 delete commands or
transactions?

At the publisher the delete was executed in 1 "delete from table" stmt. This
is obviously broken down into 270K simpleton delete sproc stmts... is this
considered 1 transaction w/ 270K commands? Sounds like I just answered my own
question...

Over a T1 is it safe to go higher than 1000 for the -CommitBatchSize distr
agent param?

Thanks,

Re: Large delete stmt sent thru Distr. Agent

Hilary Cotter
9/19/2005 9:13:45 PM

It probably is depending on the stability of your link.

My understanding is this: the default is 0 which means all commands which
form part of the transaction are committed or rolled back. A value of
CommitBatchSize means that the commands are committed at that value, and
cannot be rolled back.

So a CommitBatchSize of 1000 will mean that if your link goes down half way
through 27,000/2 of the commands will be committed. If it goes down after
the 13501 command is committed, the last command will be rolled back, the
other 13,500 will be committed.

--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
[quoted text, click to view]