"Yuri Budilov" <YuriBudilov@discussions.microsoft.com> wrote in message
news:092CFBF6-C605-45DA-BDE6-02329A65441D@microsoft.com...
>I edited the Log Reader Agent job and inserted -MaxCmdsInTran 10000 there,
> the log reader appeared to start but new row changes are no longer
> flowing. I
> did not start profiler. Can you provide me with a working example perhaps?
> Does the position of the parameter make a difference? I inserted it at the
> end of the command.
>
> Here is the original command I edited: (all on one line in log reader
> agent
> job, step 2)
>
> -Publisher [SQLTST\Inst1] -PublisherDB [marsx] -Distributor [SQLTST\Inst1]
> -DistributorSecurityMode 1 -Continuous
>
> I added -MaxCmdsInTran 10000 in front of -Continuous and re-started the
> agent.
>
> New Command:
> -Publisher [SQLTST\Inst1] -PublisherDB [marsx] -Distributor [SQLTST\Inst1]
> -DistributorSecurityMode 1 -MaxCmdsInTran 10000 -Continuous
>
> No more new replicated transactions.
>
> So I removed the MaxCmdsInTran and restarted the agent and it is not doing
> anything still, showing "AWAITING COMMAND" and no new transactions are
> flowing so it sounds like I have to re-init them all now. It was working
> well
> before I tried this parameter.
>
> Overall I have done a lot of Transactional Replication work on SQL 2000
> SP2-3 in distant past and it always worked well for me. Now on SQL 2005
> SP2
> Transactional replication does not feel as stable to me. Trouble is it is
> very hard to reproduce problems in replication.
>
> Thanks.
> --
> Yuri Budilov
> Melbourne, Australia
>
>
> "Hilary Cotter" wrote:
>
>> It was never in the profiles. It's an optional parameter you can add to
>> the
>> agent job. It should work there. Did you run profiler to see what was
>> going
>> on?
>>
>> --
>> RelevantNoise.com - dedicated to mining blogs for business intelligence.
>>
>> 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
>> "Yuri Budilov" <YuriBudilov@discussions.microsoft.com> wrote in message
>> news:A2DC8FC6-0086-4DDA-AEC5-19D56A345742@microsoft.com...
>> > Hello Everyone
>> >
>> > This is cross posted in MSDN Forums....
>> >
>> > SQL 2005 SP2 Dev/Ed, 32-bit. Transactional replication,
>> > R/O, with no filters of any kind.
>> >
>> > in Log Reader Agent profiles I can not find MaxCmdsInTran parameter
>> > listed
>> > anywhere in SQL ManStudio UI.
>> >
>> > What am I missing? I look under distributor properties, profile
>> > defaults,
>> > log reader agent and then I clear the "Show only parameters used". Does
>> > it
>> > now go by another name in UI or no longer supported in SQL2005 even
>> > though
>> > BOL mentions it? Is it only available via direct execution of
>> > logread.exe
>> > perhaps, and not UI/Agent job?
>> >
>> > The problem I am trying to solve is that I have transactional
>> > replication
>> > from a reporting "staging" data mart to one or two purely reporting
>> > data
>> > marts so ACID properties are not important but I want to replicate
>> > millions
>> > of (short) fact rows as quickly as possible from distribution db to one
>> > or
>> > more reporting db (on same cluster or fast network). I thought this
>> > parameter
>> > could be of some performance use to me. As I recall it existed in SQL
>> > 2000
>> > days, added in a service pack 2 or 3.
>> >
>> > I tried to manually add it to the Agent job and the job stopped working
>> > so
>> > I
>> > took it out. Perhaps I have done it wrong. The agent seemed to hang
>> > with
>> > no
>> > helpful error messages as I can recall at the moment.
>> >
>> > Database Mirror or Snapshot DB do not suit me because I need to
>> > replicate
>> > at
>> > a fact table level (possibly row filtered in future) so Transactional
>> > Replication works nicely, if slow for such volumes. I can not replicate
>> > stored procedure execution either, only rows of fact data.
>> >
>> >
>> > thank you in advance
>> > Yuri Budilov
>> > Melbourne, Australia
>>
>>
>>