<zerg2k@yahoo.com> wrote in message
news:1160776334.883628.186830@m73g2000cwd.googlegroups.com...
> Hilary I have tried with Bulk Logged and Simple.
>
> Running Truncations every minute, on BL or check pointing in simple IT
> does *not* work!
>
> To top it all off I was able to run my servers in 2000 with no issues
> at all!!!
>
> I runned a trace on how log space is being handled on 2005 against 2000
> and I have to tell you that in 2000 you get to see for example that log
> truncations grow linearly an in 2005 *nothing* is hapenning until each
> bcp file boundary.
>
> I have a workaround that is to use the -UseInprocLoader switch on the
> distribution agent but unfortunatley is "deprecated". I could still
> use it but I wold have to tell my developers that they have to forget
> about the xml datatype... *not* acceptable in 2005, right ?
>
> Hope you can help us.
>
> Noel
> Sr. DBA
>
> Hilary Cotter wrote:
>> Put your database in bulk logged recovery model before starting the apply
>> the snapshot. The transaction log will not grow as large and the snapshot
>> will be applied faster.
>>
>> --
>> 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
>>
>>
>>
>> <zerg2k@yahoo.com> wrote in message
>> news:1160774149.907286.193840@m7g2000cwm.googlegroups.com...
>> >I am starting this thread in hopes that someone has seen this same
>> > problem.
>> >
>> > I have transactional replication set up in many of my servers but in
>> > 2005 I have a big issue when the Distributor is applying the initial
>> > snapshot. Because MS decided to implement the "nice" concurrent
>> > snapshot I get several bcp files per table (16 to be exact). When the
>> > size of those files is evenly distributed my transaction log (AT
>> > SUBSCRIBER) grows to almost half of the DB File Size, but when one of
>> > those bcp files get unproportionally large my Transaction Log grows at
>> > almost 5 times the size of the Database file.
>> >
>> > There is definitely something wrong with the transaction log handling
>> > on 2005 which MS does not want to admit. My 2000 Servers for the same
>> > database work perfectly well when applying the Initial Snapshot.
>> >
>> > I know that the default profile settings are incorrectly documented or
>> > correctly documented but implemented differenly :-) (BUG1). I have
>> > tried with my "same" settings of 2000 and when the data unbalance
>> > happens I get hit by the transaction log issue.
>> >
>> > More details: Subscriber database in Bulk-Logged Mode, Win2K3 x64 SP1,
>> > SS 2K5 SP1.
>> >
>> > Have someone come across this ? I can't believe I am the only one.
>> > This has happened in many of my servers, therefore I discount Hardware
>> > as the root of the problem.
>> >
>> > Thanks in advance.
>> >
>

zerg2k@yahoo.com wrote:
> Raymond,
>
> I appreciate your HONEST response.
> I am not complaining about the unevenly distribution of the bcp files,
> I couldn't care less *if*
> when the files were applied by bcp at receiving end, the transaction
> log would behave appropriately.
> The uneven distribution just makes it look worse. In 2000 we just got a
> single large file per table, right?
> So that is not really an issue.
>
> Thank you VERY MUCH for the update statistics first tip I'll try that
> ASAP.
>
> In regards to your suggestion of dropping the table, I am currently
> doing it .That, is one of the reasons that
> I was really worried about because the log on the subscriber is *not*
> working in
> Truncation mode or if it is, is doing a BAD job.
>
> The only thing special about my data is that my tables are LARGE (~600
> million rows) and narrow (5 cols)
> The skewness of the data is a possibility, we model many things of the
> real world. I thank you ONCE AGAIN for the
> Undocumented parameter for the bcp process because I did try to set it
> back to native but bcp partitioning was
> still happening and not only that, my database was being demoted to
> compatibility level 8.0 ...*not* good
>
> So, Because of your help and *position at MS* I would really have to
> ask you if you could dig
> somehow with the folks in charge of transaction log handling to know
> what is really going on.
>
> People like you are the ones that make a company look good!
>
> Thank you VERY MUCH for your recomendations.
>
> Noel
> Sr. DBA
>
>
> Raymond Mak [MSFT] wrote:
> > Since replication uses certain terms in a highly specific manner, I just
> > want to clarify a few things just to make sure that we are on the same page:
> >
> > 1) concurrent snapshot (sync_method)- this actually corresponds to the "Do
> > not lock tables while snapshot is generated" check-box in SQL2000 SEM. The
> > idea behind enabling this option is to tell the snapshot agent to "try" not
> > to take out locks on published tables for "extended period of time" so
> > changes can be made while the snapshot is being generated. But since locking
> > is ultimately decided by the database engine, the labelling in SEM is
> > misleading in a way that has caused me no end of troubles. Note that
> > 'concurrent snapshot' is the default sync_method option in SQL2005 and the
> > only way to change it is through the sp_changepublication stored procedure.
> > In SQL2005 SP2, we also officially support the sync_method option of
> > 'database snapshot' on Enterprise Edition (the option has been available in
> > unofficial capacity since SQL2005 RTM) which will direct the snapshot agent
> > to generate the replication snapshot from a database snapshot. This option
> > should theoretically provide lower locking contention compared with
> > concurrent snapshot while avoiding some of the complications (yes, bugs)
> > that may arise from using concurrent snapshot.
> > 2) If I am not mistaken, the "concurrent snapshot" that you mentioned is a
> > new feature in SQL2005 that I tend to refer to as "bcp partitioning" which I
> > described in some details in the following posting:
> >
> > http://groups.google.com/group/microsoft.public.sqlserver.replication/browse_thread/thread/f6bd52f0d904d0a6/6286ee80b59d65c3?lnk=gst&q=Raymond+Mak+partitioning&rnum=1#6286ee80b59d65c3
> >
> > Now, since I am nowhere near the transactional log code, I cannot comment on
> > whether there is a problem there or not. But given what you have mentioned,
> > I can perhaps offer the following suggestions (which you may or may not find
> > useful):
> >
> > 1. If the snapshot agent decides to break up a table into multiple pieces,
> > the only way you will get the full benefit of minimal logging when the
> > distribution agent applies the snapshot is 1) to have -MaxBcpThreads > 1 for
> > the distribution agent *and* 2) the target table doesn't have any indexes on
> > it before the distribution agent delivers the snapshot or you use "drop
> > table" as the article pre-creation command.
> > 2. Given that you mentioned some of your tables are not partitioned by the
> > snapshot agent evenly, you may want to update statistics on the clustered
> > index of those tables before generating the snapshot. It is indeed my fault
> > for not trying to update the statistics automatically as I couldn't find a
> > way to figure out whether the statistics has become very inaccurrate and
> > updating statistics is a very costly operation that snapshot agent should
> > probably not do every time it runs.
> > 3. Now, it is also possible that the key\data (long blobs) distribution of
> > some of your tables are inherently skewed. If this is the case, you should
> > consider specifying the unofficial\undocumented
> > "-EnableArticleBcpPartitioning 0" switch to the snapshot agent to disable
> > bcp partitioning altogether.
> >
> > FWIW, I am really sorry that my less than perfect attempt to improve the
> > product (there were indeed feedbacks from other customers that replication
> > snapshot should attempt to process a large table in parallel) has cause
> > problems in your case.
> >
> > -Raymond
> > <zerg2k@yahoo.com> wrote in message
> > news:1160774149.907286.193840@m7g2000cwm.googlegroups.com...
> > >I am starting this thread in hopes that someone has seen this same
> > > problem.
> > >
> > > I have transactional replication set up in many of my servers but in
> > > 2005 I have a big issue when the Distributor is applying the initial
> > > snapshot. Because MS decided to implement the "nice" concurrent
> > > snapshot I get several bcp files per table (16 to be exact). When the
> > > size of those files is evenly distributed my transaction log (AT
> > > SUBSCRIBER) grows to almost half of the DB File Size, but when one of
> > > those bcp files get unproportionally large my Transaction Log grows at
> > > almost 5 times the size of the Database file.