distrib. agent remains stopped

We have a situation where if the subscriber becomes unavailable for an
extended period (say over 15 minutes), the distribution agent at the
publisher will make several attempts to re-connect to the subscriber. Once
the subscriber returns to service, the distrib. agent remains in 'failed'
status and does not reconnect to the subscriber. This causes a push and
other pending operations to fail. As a workaround, the distrib. agent. has
to be manually restarted so normal operation can continue. Is this the
default behavior or possibly a known issue.
================================================== ===
Win2003/SQLSvr2000 ver 8.00 760 (SP3)/Transactional Replic. w/updatable
subscribers/
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
"batuka" <abraxas1969@.hotmail.com> wrote in message
news:ulzKd7ZdFHA.1684@.TK2MSFTNGP09.phx.gbl...
> We have a situation where if the subscriber becomes unavailable for an
> extended period (say over 15 minutes), the distribution agent at the
> publisher will make several attempts to re-connect to the subscriber. Once
> the subscriber returns to service, the distrib. agent remains in 'failed'
> status and does not reconnect to the subscriber. This causes a push and
> other pending operations to fail. As a workaround, the distrib. agent. has
> to be manually restarted so normal operation can continue. Is this the
> default behavior or possibly a known issue.
>
> ================================================== ===
> Win2003/SQLSvr2000 ver 8.00 760 (SP3)/Transactional Replic. w/updatable
> subscribers/
>
|||schedule your distribution agent to start every 5 or 15 minutes. Then make
sure it runs continuously. This way when it stops it will restart 5-15
minutes later. You can also have the job return to stop 1 on failure of step
3.
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
"batuka" <abraxas1969@.hotmail.com> wrote in message
news:ulzKd7ZdFHA.1684@.TK2MSFTNGP09.phx.gbl...
> We have a situation where if the subscriber becomes unavailable for an
> extended period (say over 15 minutes), the distribution agent at the
> publisher will make several attempts to re-connect to the subscriber. Once
> the subscriber returns to service, the distrib. agent remains in 'failed'
> status and does not reconnect to the subscriber. This causes a push and
> other pending operations to fail. As a workaround, the distrib. agent. has
> to be manually restarted so normal operation can continue. Is this the
> default behavior or possibly a known issue.
>
> ================================================== ===
> Win2003/SQLSvr2000 ver 8.00 760 (SP3)/Transactional Replic. w/updatable
> subscribers/
>
|||Both of the suggested options worked very well. However, none of the db
updates that were done at the publisher (while the distribution agent
remained in 'stopped' status) are reflected ('or pushed') at the subscriber
db when it returned to service. Any ideas are apprecciated.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OxUzufadFHA.3052@.TK2MSFTNGP10.phx.gbl...
> schedule your distribution agent to start every 5 or 15 minutes. Then make
> sure it runs continuously. This way when it stops it will restart 5-15
> minutes later. You can also have the job return to stop 1 on failure of
step[vbcol=seagreen]
> 3.
> --
> 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
> "batuka" <abraxas1969@.hotmail.com> wrote in message
> news:ulzKd7ZdFHA.1684@.TK2MSFTNGP09.phx.gbl...
Once[vbcol=seagreen]
'failed'[vbcol=seagreen]
has
>

Distrib agent question

From BOL
CommitBatchThreshold 1000 The number of replication commands to be
issued to the Subscriber before a COMMIT statement is issued.
So if the distrib agent report 1 transaction with 100,000 commands are
replicated, i guess the above CommitBatchThreshold of 1000 does not apply..
right ? It appears that it commits only after the 100,000 commands make it
to the subscriber . Am I right ? Can i commit on the subscriber 1000
commands at a time ?
Not really, one commit will be called. One commit will occur in increments
of 1000, so one commit will be called from 1 to 1000 transactions. For 1001
to 2000 there will be 2, 2000>3000 3, etc.
The commit is done on a per transaction basis as opposed to a per command
basis.
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
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%232IwOPmYFHA.2076@.TK2MSFTNGP15.phx.gbl...
> From BOL
> CommitBatchThreshold 1000 The number of replication commands to be
> issued to the Subscriber before a COMMIT statement is issued.
>
> So if the distrib agent report 1 transaction with 100,000 commands are
> replicated, i guess the above CommitBatchThreshold of 1000 does not
> apply..
> right ? It appears that it commits only after the 100,000 commands make it
> to the subscriber . Am I right ? Can i commit on the subscriber 1000
> commands at a time ?
>
|||I have 1 transaction with 100,000 commands..
Do you mean it will commit 1000 commands at a time..
Im a lil confused as your message indicates transactions and not commands
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:O4Y1LzRZFHA.2768@.tk2msftngp13.phx.gbl...
> Not really, one commit will be called. One commit will occur in increments
> of 1000, so one commit will be called from 1 to 1000 transactions. For
1001[vbcol=seagreen]
> to 2000 there will be 2, 2000>3000 3, etc.
> The commit is done on a per transaction basis as opposed to a per command
> basis.
> --
> 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
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:%232IwOPmYFHA.2076@.TK2MSFTNGP15.phx.gbl...
it
>
|||The way I read it: one commit, no matter how many commands per transaction.
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
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uaN5P6SZFHA.1368@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> I have 1 transaction with 100,000 commands..
> Do you mean it will commit 1000 commands at a time..
> Im a lil confused as your message indicates transactions and not commands
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:O4Y1LzRZFHA.2768@.tk2msftngp13.phx.gbl...
increments[vbcol=seagreen]
> 1001
command[vbcol=seagreen]
be[vbcol=seagreen]
are[vbcol=seagreen]
make
> it
>

Dist Agent Setting

Running Win 2003 & SQL 2005
I had a problem with one of my dist agents & got backed up to the tune
of 1.55 million commands in the distribution database waiting to be
applied.
The agent has been running for 9+ hours and the number of pending
commands has not changed.
Is there a setting I can chang/make in the dist agent to get them to
process faster or atleast see the number of pending go down, so I will
know it is working.
AHIA,
Larry...
Larry - you could try reducing the -CommitBatchSize
nd -CommitBatchThreshold parameters and see if this helps.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com