Re: Distribution Agent Startup Parameters

freaking Google.

--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.

This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.

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



"Raymond Mak [MSFT]" <rmak@xxxxxxxxxxxxxxxxxxxx> wrote in message
news:enfPtCt8GHA.1492@xxxxxxxxxxxxxxxxxxxxxxx
Wait a minute, I think you are the one who owe me a beer, and I have proof
for that...

http://groups.google.com/group/microsoft.public.sqlserver.replication/browse_frm/thread/6fc9607d718dd9d3/2e5c299fb7328e2d?lnk=gst&q=Raymond+Mak&rnum=27#2e5c299fb7328e2d

"Hilary Cotter" <hilary.cotter@xxxxxxxxx> wrote in message
news:%23yI5FQr8GHA.3820@xxxxxxxxxxxxxxxxxxxxxxx
Ok, got it! Thanks Raymond. Still waiting on that beer you owe me;)

--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.

This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.

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



"Raymond Mak [MSFT]" <rmak@xxxxxxxxxxxxxxxxxxxx> wrote in message
news:Oy1jKJl8GHA.2120@xxxxxxxxxxxxxxxxxxxxxxx
Hilary, what you said is true, but the goal of my reccomendations is not
to have small transactions but to hit the minimal logging (or
bulk-logged) code path in the server which is the only feasible way to
have a manageable log size when a concurrent snapshot is applied in
SQL2000. If a small batch size is specified, the server will see the
target table as non-empty after the first batch is loaded and start
going down the fully-logged code path after that. And since there is an
outer transaction bounding the delivery of a concurrent snapshot in
SQL2000, the log simply cannot be truncated even with a small bcp batch
size. I can understand why you think this is counter-intuitive, but my
suggestion had worked on a few occasions before including the following:

http://groups.google.com/group/microsoft.public.sqlserver.replication/browse_frm/thread/7e366c835c5323f2/1779e49f1fb79fe5?tvc=1&q=robinson+Raymond+Mak#1779e49f1fb79fe5

-Raymond

"Hilary Cotter" <hilary.cotter@xxxxxxxxx> wrote in message
news:uuGAtgk8GHA.2316@xxxxxxxxxxxxxxxxxxxxxxx
Hi Raymond, I though bcpBatchSize is analogous to the batchsize in bcp,
which means that it controls how many rows will be applied at a time in
a batch. Won't setting it to a large value create a very large
transaction log for large tables?

--
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

"Raymond Mak [MSFT]" <rmak@xxxxxxxxxxxxxxxxxxxx> wrote in message
news:evddMLk8GHA.4084@xxxxxxxxxxxxxxxxxxxxxxx
If you are using concurrent snapshot on SQL2000, your only hope will
be to make sure that you hit the minimal logging code path when the
snapshot is delivered to the subscriber as the *entire* snapshot will
be applied in an atomic transaction. At the risk of telling you
something that you may know already, this means that you need to:

1) Make sure that 'select into\bulk copy' is enabled at the subscriber
database
2) The target table is empty with at most one index or not having any
indexes when the bulk copy occurs. This may be tricky to enforce as
the SQL2000 index creation logic in the distribution agent is not very
precise. You may want to modify the .idx scripts to leave only the
primary key index and then manually create other indexes afterwards.
3) Set -BcpBatchSize to be *larger* than the maximum number of rows in
your published tables. 2^31 - delta should work fine. Notice that you
may start seeing "Agent is waiting for database backend.." messages
since the distribution agent will not be able to log any progress
messages until the entire bulk load operation is done. You have also
want to increase the -QueryTimeout of the distribution agent.

-Raymond

"John T" <JohnT@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:469D5803-1C9E-447E-9513-2AE04EFEB305@xxxxxxxxxxxxxxxx
Hello,

I have scenario where I use two servers: SQL 2K sp4, on Win2K3 sp1.
One
server is a publisher, the other a subscriber. The replication
topology is
Transaction Replication with 1 queued updating subscriber. The
distribution
agent resides on the publisher.

Firstly, I am aware of nosync replication, but my environment
requires some
more testing before I can implement it, because I can't take the
publisher
offline, and I believe some tweaks are needed to ensure that the data
will
truly be in sync. This said, one of my publications has a table
article with
234 million records to transfer when the article is reinitialized. I
am
having problems with the transaction log growing so large on the
subscriber,
that it consumes all server space, and effectively kills the
distribution
agent.

To mitigate this, I have tried to use the -BcpBatchSize parameter to
try to
get the rows per batch down to manageable size so that I can manually
shrink
the transaction log intermittantly while the distribution agent is
applying
the snapshot. My strategy is not working.

Questions:
1) does -BcpBatchSize directly control the application of the
snapshotted
article's transaction commit size, or is this parameter used outside
of the
distribution agent's application of a snapshot?
2) does using -UseInprocLoader affect the -BcpBatchSize parameter? I
ask
because bulk insert is different from bcp.
3) would -CommitBatchSize be a more appropriate setting to allow me
to
actively manage the transaction log on the subscriber while this huge
table
is being transferred?

Thanks for any input,
John T












.

Relevant Pages

  • Re: Distribution Agent Startup Parameters
    ... Won't setting it to a large value create a very large transaction log ... make sure that you hit the minimal logging code path when the snapshot is ... Make sure that 'select into\bulk copy' is enabled at the subscriber ... SQL2000 index creation logic in the distribution agent is not very ...
    (microsoft.public.sqlserver.replication)
  • Re: Distribution Agent Startup Parameters
    ... transaction bounding the delivery of a concurrent snapshot in SQL2000, ... SQL2000 index creation logic in the distribution agent is not very ... having problems with the transaction log growing so large on the ...
    (microsoft.public.sqlserver.replication)
  • Re: Distribution Agent Startup Parameters
    ... manageable log size when a concurrent snapshot is applied in SQL2000. ... Won't setting it to a large value create a very large transaction ... snapshot is delivered to the subscriber as the *entire* snapshot will be ... SQL2000 index creation logic in the distribution agent is not very ...
    (microsoft.public.sqlserver.replication)
  • Re: Distribution Agent Startup Parameters
    ... If I choose the schema option to drop the tables before the snapshot is ... Make sure that 'select into\bulk copy' is enabled at the subscriber ... SQL2000 index creation logic in the distribution agent is not very ... the transaction log intermittantly while the distribution agent is ...
    (microsoft.public.sqlserver.replication)
  • Re: Distribution Agent Startup Parameters
    ... If I choose the schema option to drop the tables before the snapshot is ... Make sure that 'select into\bulk copy' is enabled at the subscriber ... SQL2000 index creation logic in the distribution agent is not very ... the transaction log intermittantly while the distribution agent is ...
    (microsoft.public.sqlserver.replication)
Quantcast