distrib.exe Many instances running

How many instances of distrib.exe should be running?

Yesterday I created multiple publications in SQL 2005. without counting, it looks like each publication has its own distrib.exe.... it this the way it is supposed to work?

Each instance it holding 8k of ram (not a problem right now) but I have many more publications to create...

Please let me know if this is the way SQL server works now?

Hi William,

I am guessing you were used to different behavior on SQL2000 where only one instance of distrib.exe is used to service multiple publications\subscriptions to the same subscriber database. The new behavior in SQL2005 is actually the result of a change in the default value of the 'independent_agent' publication property from 0 (false) to 1 (true). So, you can get the same behavior as SQL2000 by changing the 'independent_agent' property from 1 to 0 via sp_changepublication. I am also a bit curious to know why you are creating many publications. If your goal is to have finer granularity for subscription re-initialization, you can actually perform article level reinitialization using sp_reinitsubscription.

-Raymond

|||

Thanks for the explanation. I just wanted to make sure it was normal...

The reason for the multiple publications is not for the purpose of the subscriptions but rather the publications them selves. We have about 200 table that are changing several times a year based (data lenghts or additional columns). The table are bundles together based on when they are anticipated to change.

This is the explanation I was given when I was hired. I think it is just to make it easier to see what is what without opening the publications.

distrib.exe & logread.exe

Win & SQL 2K
What is distrib.exe? I have it running 15 times in my
task manager. It this a prog that distributes(pushes)
transactions to subscribers? Is this an SQL thing or
should I go look else where.
Also logread.exe is running 3 times. Is this anothere
SQL thing?
Larry...
Larry,
these are the 'engines' behind replication. When BOL talks about the
distribution agent, there is a SQL Agent job whose second step starts the
executable distrib.exe. For the logreader it is logread.exe. In
troubleshooting it is common practice to start the distrib.exe executable
from the commandline, especially when dealing with MSDE where there aren't
the graphical interfaces.
HTH,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||thanks for the info Paul!!!

distrib.exe

Does the distrib.exe make a connection to SQL and read all the
trans/commands from the distrubution database to push to the subscribers ?
Yes! It reads from MSrepl_transactions and MSrepl_commands, although push or
pull obviously relates to where the agent is being run.
Rgds,
Paul Ibison
sql

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
>