Re: MaxCmdsInTran in SQL 2005 SP2

you put it in the correct position. Perhaps there is another problem which
has made the log reader skip transactions.

--
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@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:092CFBF6-C605-45DA-BDE6-02329A65441D@xxxxxxxxxxxxxxxx
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@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:A2DC8FC6-0086-4DDA-AEC5-19D56A345742@xxxxxxxxxxxxxxxx
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





.

Relevant Pages

  • Re: Deadlock between Distribution Agent and Distribution Agent Cle
    ... Another option will be to put your distribution database in bulk copy ... Looking for a SQL Server replication book? ... The Distribution Agent Cleanup job code ... and only reading the transactions committed some ...
    (microsoft.public.sqlserver.replication)
  • Re: Deadlock between Distribution Agent and Distribution Agent Cle
    ... commands being written to this table by the log reader. ... and only reading the transactions committed some time ... This proc is actually fired by the distribution agent. ... Looking for a SQL Server replication book? ...
    (microsoft.public.sqlserver.replication)
  • Re: Transactional replication and the publishers transaction log
    ... for the replication this is a good ... the un-replicated transactions that was holding back the MinLSN. ... question I’m trying to find out how the log reader advances trough the log. ... I think I’m slowly answering my self: Checkpoint needs the log reader to ...
    (microsoft.public.sqlserver.replication)
  • Re: turn off Dist agent after pending transactions are committed
    ... Replication is running. ... transactions for this table. ... agent back on I will be in trouble. ... >> Continuous switch in the Agent, If I just remove that, I ...
    (microsoft.public.sqlserver.replication)
  • Re: Theory Question - Logreader Resiliency
    ... They remain in the log until the log reader has read them, ... Looking for a SQL Server replication book? ... it is not connected to the publisher transactions occur with the ...
    (microsoft.public.sqlserver.replication)
Loading