We have configured transactional replication with queue updating
subscriptions. The publisher/distributor on serverA pushes a small db
publication to the subscriber on serverB. When both the publisher and
subscriber are continuously connected, modified data (on either server) is
replicated back and forth just fine. During our tests we decided to manually
stop the SQL server instance on the publisher (which also stops the SQLAgent
service on the publisher) and make data changes on the subscriber. Since the
publisher is unavailable, transactions on the subscriber are put in a queue
per design. When we then manually restart the publisher SQL Server instance,
few strange things happen:
- the SQLServerAgent service on the publisher is not started automatically.
- once we manually start the SQLServerAgent service on the publisher, the
QUEUE Reader Agent on the subscriber fails to start with a series of error
messages like this:
=============
"Server: ServerA, Database mydb : ODBC Error:Could not find stored procedure
''?$?Aw???A?.??''.
"Failed while applying queued message to publisher"
"Queue Reader aborting. The step failed"
=============
In this scenario, the Queue Reader agent remains in "Failed" status (it
doesn't restarts) and therefore the data changes previoulsy made on the
subscriber are not propagated back to the publisher.
Once we manually restart the Queue Reader agent on the publisher, the
changed data is replicated to the subscriber successfully.
Our environment is:
Windows 2003 Server ED
SQL Server 2000 SP3
Is this the expected behavior? Ideas?
can you locate this stored procedure? Is it where it should be?
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:O4hT1zLTFHA.3540@.TK2MSFTNGP10.phx.gbl...
> We have configured transactional replication with queue updating
> subscriptions. The publisher/distributor on serverA pushes a small db
> publication to the subscriber on serverB. When both the publisher and
> subscriber are continuously connected, modified data (on either server) is
> replicated back and forth just fine. During our tests we decided to
manually
> stop the SQL server instance on the publisher (which also stops the
SQLAgent
> service on the publisher) and make data changes on the subscriber. Since
the
> publisher is unavailable, transactions on the subscriber are put in a
queue
> per design. When we then manually restart the publisher SQL Server
instance,
> few strange things happen:
>
> - the SQLServerAgent service on the publisher is not started
automatically.
> - once we manually start the SQLServerAgent service on the publisher, the
> QUEUE Reader Agent on the subscriber fails to start with a series of error
> messages like this:
>
> =============
> "Server: ServerA, Database mydb : ODBC Error:Could not find stored
procedure
> ''?$?Aw???A?.??''.
> "Failed while applying queued message to publisher"
> "Queue Reader aborting. The step failed"
> =============
>
> In this scenario, the Queue Reader agent remains in "Failed" status (it
> doesn't restarts) and therefore the data changes previoulsy made on the
> subscriber are not propagated back to the publisher.
>
> Once we manually restart the Queue Reader agent on the publisher, the
> changed data is replicated to the subscriber successfully.
>
> Our environment is:
> Windows 2003 Server ED
> SQL Server 2000 SP3
>
> Is this the expected behavior? Ideas?
>
>
|||I couldn't locate it but by looking at this MS article it appears to be the
fix for this. Only that SP4 doesn't seem to be available any time soon I
believe.
http://support.microsoft.com/default...b;en-us;884854
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23PojRkMTFHA.2560@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> can you locate this stored procedure? Is it where it should be?
> --
> 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:O4hT1zLTFHA.3540@.TK2MSFTNGP10.phx.gbl...
is[vbcol=seagreen]
> manually
> SQLAgent
> the
> queue
> instance,
> automatically.
the[vbcol=seagreen]
error
> procedure
>
Showing posts with label publisher. Show all posts
Showing posts with label publisher. Show all posts
Saturday, February 25, 2012
Queue Reader fails occasionally
I am using transactional replication with queued updating between two servers
running SQL Server 2000 SP3. Publisher and distributor run both on the same
server (a cluster machine). There is one subscriber. The Queue Reader Agent
starts successfully, and transactions originating on either side are properly
replicated. However, the Queue Reader occasionally fails. I enabled logging,
but the output file looks normal to me; several queries for queued data, but
then it seems to timeout. It just sits there for 3 minutes, then fails and
retries. No error message in the output file. Finally (after specified count
of retries) the agent shuts down with the message "Remote procedure call
failed". When I try to restart Queue Reader Agent, same behavior. I have to
examine the queue (table MSreplication_queue) and delete the first
respectively first few rows. Before deleting I execute the corresponding
queries (sp calls of type sp_MSsync_upd_MyTable) in Query Analyzer, what
succeeds. After deleting the rows from MSreplication_queue I can restart
Queue Reader Agent and now all works properly up to the next fail. :-(
Does anyone have any advice? Thanks in advance
Dorrit
What if you run these commands manually from the subscriber using QA? What
I'm thinking is that it might be a locking (blocking) issue which you'd be
able to determine using sp_who2 at the publisher.
HTH
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I'm not sure whether I did understand your reply correctly. What I usually do
to solve the problem is running these commands manually at the publisher
using QA. (I simply copy them from the output file.). This functions properly,
I can even track this by querying the correspondent rows at the subscriber
and at the publisher. Before executing the commands in QA, the changes are
only at the subscriber, afterwards they are at both the subscriber and the
publisher. That's why I think it cannot be a locking issue.
Message posted via http://www.droptable.com
|||If it's definitely not blocking then I'm not too sure. You could use
profiler on the publisher to check the commands are getting that far when
running the queue reader agent. You could also simultaneously run profiler
on teh subscriber to confirm the queue is being read.
Rgds,
Paul Ibison, SQL Server MVP
|||OK, I think you could be right. I'll try then with profiler...
Thanks, Dorrit
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...ation/200507/1
running SQL Server 2000 SP3. Publisher and distributor run both on the same
server (a cluster machine). There is one subscriber. The Queue Reader Agent
starts successfully, and transactions originating on either side are properly
replicated. However, the Queue Reader occasionally fails. I enabled logging,
but the output file looks normal to me; several queries for queued data, but
then it seems to timeout. It just sits there for 3 minutes, then fails and
retries. No error message in the output file. Finally (after specified count
of retries) the agent shuts down with the message "Remote procedure call
failed". When I try to restart Queue Reader Agent, same behavior. I have to
examine the queue (table MSreplication_queue) and delete the first
respectively first few rows. Before deleting I execute the corresponding
queries (sp calls of type sp_MSsync_upd_MyTable) in Query Analyzer, what
succeeds. After deleting the rows from MSreplication_queue I can restart
Queue Reader Agent and now all works properly up to the next fail. :-(
Does anyone have any advice? Thanks in advance
Dorrit
What if you run these commands manually from the subscriber using QA? What
I'm thinking is that it might be a locking (blocking) issue which you'd be
able to determine using sp_who2 at the publisher.
HTH
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I'm not sure whether I did understand your reply correctly. What I usually do
to solve the problem is running these commands manually at the publisher
using QA. (I simply copy them from the output file.). This functions properly,
I can even track this by querying the correspondent rows at the subscriber
and at the publisher. Before executing the commands in QA, the changes are
only at the subscriber, afterwards they are at both the subscriber and the
publisher. That's why I think it cannot be a locking issue.
Message posted via http://www.droptable.com
|||If it's definitely not blocking then I'm not too sure. You could use
profiler on the publisher to check the commands are getting that far when
running the queue reader agent. You could also simultaneously run profiler
on teh subscriber to confirm the queue is being read.
Rgds,
Paul Ibison, SQL Server MVP
|||OK, I think you could be right. I'll try then with profiler...
Thanks, Dorrit
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...ation/200507/1
Queue Reader fails
I'm running a clustered distributor on SQL 2000 sp3a.
When I stop the publisher that runs in the same cluster
(diffrent SQL server) the Queue reader first logs
"ODBC Error:SQL Server does not exist or access denied."
(It's dead) then it says "Unable to connect to publisher"
and finaly it says "Queue Reader aborting step failed"
and then the queue reader fails.
Any ideas why the queue reader fails?
Is your Queue Reader on the Publisher or Subscriber?
You might want to do some logging to see what the error message is. Follow
the steps in the below link:
http://support.microsoft.com/default...b;EN-US;312292
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Peter Ericsson" <peter.l.ericsson@.ericsson.com> wrote in message
news:29b3201c4658d$2f264d90$a401280a@.phx.gbl...
> I'm running a clustered distributor on SQL 2000 sp3a.
> When I stop the publisher that runs in the same cluster
> (diffrent SQL server) the Queue reader first logs
> "ODBC Error:SQL Server does not exist or access denied."
> (It's dead) then it says "Unable to connect to publisher"
> and finaly it says "Queue Reader aborting step failed"
> and then the queue reader fails.
> Any ideas why the queue reader fails?
|||When I enabled logging for the agent I saw something that
might have been the problem.
In the "Run Agent" step there is a "Step
name", "Type", "Database" and "Command" option.
The Database option read AMS which is one of our user
databases. I changed that to distribution (name of the
distribution database) and now it seems to work. Is this
a SQL bug?
BTW the queue reader runs on the Distributor.
>--Original Message--
>Is your Queue Reader on the Publisher or Subscriber?
>You might want to do some logging to see what the error
message is. Follow
>the steps in the below link:
>http://support.microsoft.com/default.aspx?scid=kb;EN-
US;312292
>--
>Hilary Cotter
>Looking for a book on SQL Server replication?
>http://www.nwsu.com/0974973602.html
>
>"Peter Ericsson" <peter.l.ericsson@.ericsson.com> wrote
in message[vbcol=seagreen]
>news:29b3201c4658d$2f264d90$a401280a@.phx.gbl...
denied."[vbcol=seagreen]
publisher"
>
>.
>
|||Hi,
I had a similar issue. The Queue Reader Agent would fail, and whenever I
tried to restrart I would get the "Queue Reader aborting step failed" error.
after logging the error messages as Hillary suggested, I saw that it was
failing on startup with the following SQL error
[6/22/2004 12:54:22 PM]MAST.Bill: select top 1 id, name from
MSqreader_agents
Server: MAST, Database Bill : ODBC Error:Invalid object name
'MSqreader_agents'.
"Bill" is a user database; the table MSqreader_agents is in the distribution
database. When I looked at the job step that runs the queue reader agent, I
saw that the database dropdown was et to "Bill". This job was created by the
replication setup wizard and never updated. I changed the step to run with
the distribution database, and I was able to restart the agent. However, the
change doesn't "stick"; if I go back and look at the job step, it's set to
run under the Bill" database.
I first thought something on my server had gotten corrupted, but it sounds
like you have the same problem.
If anyone from Microsift reads this newsgroup, please chime in!
Ed
"Peter Ericsson" <peter.l.ericsson@.ericsson.com> wrote in message
news:299fd01c465ad$498c1000$a501280a@.phx.gbl...[vbcol=seagreen]
> When I enabled logging for the agent I saw something that
> might have been the problem.
> In the "Run Agent" step there is a "Step
> name", "Type", "Database" and "Command" option.
> The Database option read AMS which is one of our user
> databases. I changed that to distribution (name of the
> distribution database) and now it seems to work. Is this
> a SQL bug?
> BTW the queue reader runs on the Distributor.
> message is. Follow
> US;312292
> in message
> denied."
> publisher"
|||can either of you script out your tables, publications, subscriptions so I
or MS folks can repro this?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
<ed.d@.osgbilling.com> wrote in message
news:erbmp3eZEHA.3508@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I had a similar issue. The Queue Reader Agent would fail, and whenever I
> tried to restrart I would get the "Queue Reader aborting step failed"
error.
> after logging the error messages as Hillary suggested, I saw that it was
> failing on startup with the following SQL error
> [6/22/2004 12:54:22 PM]MAST.Bill: select top 1 id, name from
> MSqreader_agents
> Server: MAST, Database Bill : ODBC Error:Invalid object name
> 'MSqreader_agents'.
> "Bill" is a user database; the table MSqreader_agents is in the
distribution
> database. When I looked at the job step that runs the queue reader agent,
I
> saw that the database dropdown was et to "Bill". This job was created by
the
> replication setup wizard and never updated. I changed the step to run with
> the distribution database, and I was able to restart the agent. However,
the
> change doesn't "stick"; if I go back and look at the job step, it's set to
> run under the Bill" database.
> I first thought something on my server had gotten corrupted, but it sounds
> like you have the same problem.
> If anyone from Microsift reads this newsgroup, please chime in!
> Ed
>
> "Peter Ericsson" <peter.l.ericsson@.ericsson.com> wrote in message
> news:299fd01c465ad$498c1000$a501280a@.phx.gbl...
>
|||I don't think it has anything to do with our publications
or subscriptions my guess is that both Ed and I have user
database starting in my case with A (AMS) and in Ed's
case B (Bill) and they both are sorted before D
(distribution). So my guess is that if you add a database
at the distributor with a name that sorts
before "distribution" you will see the problem.
/Peter
>--Original Message--
>can either of you script out your tables, publications,
subscriptions so I[vbcol=seagreen]
>or MS folks can repro this?
>--
>Hilary Cotter
>Looking for a book on SQL Server replication?
>http://www.nwsu.com/0974973602.html
>
><ed.d@.osgbilling.com> wrote in message
>news:erbmp3eZEHA.3508@.TK2MSFTNGP09.phx.gbl...
fail, and whenever I[vbcol=seagreen]
aborting step failed"[vbcol=seagreen]
>error.
I saw that it was[vbcol=seagreen]
name from[vbcol=seagreen]
object name[vbcol=seagreen]
is in the[vbcol=seagreen]
>distribution
queue reader agent,[vbcol=seagreen]
>I
job was created by[vbcol=seagreen]
>the
the step to run with[vbcol=seagreen]
the agent. However,[vbcol=seagreen]
>the
job step, it's set to[vbcol=seagreen]
corrupted, but it sounds[vbcol=seagreen]
chime in![vbcol=seagreen]
in message[vbcol=seagreen]
that[vbcol=seagreen]
the[vbcol=seagreen]
this[vbcol=seagreen]
error[vbcol=seagreen]
scid=kb;EN-[vbcol=seagreen]
wrote[vbcol=seagreen]
sp3a.[vbcol=seagreen]
cluster[vbcol=seagreen]
failed"
>
>.
>
|||Hi,
I have the same problem but I achieved that the DB that appears in log would
be the correct one and continues wrong.
I have proved what you say and it continues without working.
"Peter Ericsson" <peter.l.ericsson@.ericsson.com> escribi en el mensaje
news:2ac8f01c467ed$c0c284a0$a501280a@.phx.gbl...[vbcol=seagreen]
> I don't think it has anything to do with our publications
> or subscriptions my guess is that both Ed and I have user
> database starting in my case with A (AMS) and in Ed's
> case B (Bill) and they both are sorted before D
> (distribution). So my guess is that if you add a database
> at the distributor with a name that sorts
> before "distribution" you will see the problem.
> /Peter
> subscriptions so I
> fail, and whenever I
> aborting step failed"
> I saw that it was
> name from
> object name
> is in the
> queue reader agent,
> job was created by
> the step to run with
> the agent. However,
> job step, it's set to
> corrupted, but it sounds
> chime in!
> in message
> that
> the
> this
> error
> scid=kb;EN-
> wrote
> sp3a.
> cluster
> failed"
When I stop the publisher that runs in the same cluster
(diffrent SQL server) the Queue reader first logs
"ODBC Error:SQL Server does not exist or access denied."
(It's dead) then it says "Unable to connect to publisher"
and finaly it says "Queue Reader aborting step failed"
and then the queue reader fails.
Any ideas why the queue reader fails?
Is your Queue Reader on the Publisher or Subscriber?
You might want to do some logging to see what the error message is. Follow
the steps in the below link:
http://support.microsoft.com/default...b;EN-US;312292
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Peter Ericsson" <peter.l.ericsson@.ericsson.com> wrote in message
news:29b3201c4658d$2f264d90$a401280a@.phx.gbl...
> I'm running a clustered distributor on SQL 2000 sp3a.
> When I stop the publisher that runs in the same cluster
> (diffrent SQL server) the Queue reader first logs
> "ODBC Error:SQL Server does not exist or access denied."
> (It's dead) then it says "Unable to connect to publisher"
> and finaly it says "Queue Reader aborting step failed"
> and then the queue reader fails.
> Any ideas why the queue reader fails?
|||When I enabled logging for the agent I saw something that
might have been the problem.
In the "Run Agent" step there is a "Step
name", "Type", "Database" and "Command" option.
The Database option read AMS which is one of our user
databases. I changed that to distribution (name of the
distribution database) and now it seems to work. Is this
a SQL bug?
BTW the queue reader runs on the Distributor.
>--Original Message--
>Is your Queue Reader on the Publisher or Subscriber?
>You might want to do some logging to see what the error
message is. Follow
>the steps in the below link:
>http://support.microsoft.com/default.aspx?scid=kb;EN-
US;312292
>--
>Hilary Cotter
>Looking for a book on SQL Server replication?
>http://www.nwsu.com/0974973602.html
>
>"Peter Ericsson" <peter.l.ericsson@.ericsson.com> wrote
in message[vbcol=seagreen]
>news:29b3201c4658d$2f264d90$a401280a@.phx.gbl...
denied."[vbcol=seagreen]
publisher"
>
>.
>
|||Hi,
I had a similar issue. The Queue Reader Agent would fail, and whenever I
tried to restrart I would get the "Queue Reader aborting step failed" error.
after logging the error messages as Hillary suggested, I saw that it was
failing on startup with the following SQL error
[6/22/2004 12:54:22 PM]MAST.Bill: select top 1 id, name from
MSqreader_agents
Server: MAST, Database Bill : ODBC Error:Invalid object name
'MSqreader_agents'.
"Bill" is a user database; the table MSqreader_agents is in the distribution
database. When I looked at the job step that runs the queue reader agent, I
saw that the database dropdown was et to "Bill". This job was created by the
replication setup wizard and never updated. I changed the step to run with
the distribution database, and I was able to restart the agent. However, the
change doesn't "stick"; if I go back and look at the job step, it's set to
run under the Bill" database.
I first thought something on my server had gotten corrupted, but it sounds
like you have the same problem.
If anyone from Microsift reads this newsgroup, please chime in!
Ed
"Peter Ericsson" <peter.l.ericsson@.ericsson.com> wrote in message
news:299fd01c465ad$498c1000$a501280a@.phx.gbl...[vbcol=seagreen]
> When I enabled logging for the agent I saw something that
> might have been the problem.
> In the "Run Agent" step there is a "Step
> name", "Type", "Database" and "Command" option.
> The Database option read AMS which is one of our user
> databases. I changed that to distribution (name of the
> distribution database) and now it seems to work. Is this
> a SQL bug?
> BTW the queue reader runs on the Distributor.
> message is. Follow
> US;312292
> in message
> denied."
> publisher"
|||can either of you script out your tables, publications, subscriptions so I
or MS folks can repro this?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
<ed.d@.osgbilling.com> wrote in message
news:erbmp3eZEHA.3508@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I had a similar issue. The Queue Reader Agent would fail, and whenever I
> tried to restrart I would get the "Queue Reader aborting step failed"
error.
> after logging the error messages as Hillary suggested, I saw that it was
> failing on startup with the following SQL error
> [6/22/2004 12:54:22 PM]MAST.Bill: select top 1 id, name from
> MSqreader_agents
> Server: MAST, Database Bill : ODBC Error:Invalid object name
> 'MSqreader_agents'.
> "Bill" is a user database; the table MSqreader_agents is in the
distribution
> database. When I looked at the job step that runs the queue reader agent,
I
> saw that the database dropdown was et to "Bill". This job was created by
the
> replication setup wizard and never updated. I changed the step to run with
> the distribution database, and I was able to restart the agent. However,
the
> change doesn't "stick"; if I go back and look at the job step, it's set to
> run under the Bill" database.
> I first thought something on my server had gotten corrupted, but it sounds
> like you have the same problem.
> If anyone from Microsift reads this newsgroup, please chime in!
> Ed
>
> "Peter Ericsson" <peter.l.ericsson@.ericsson.com> wrote in message
> news:299fd01c465ad$498c1000$a501280a@.phx.gbl...
>
|||I don't think it has anything to do with our publications
or subscriptions my guess is that both Ed and I have user
database starting in my case with A (AMS) and in Ed's
case B (Bill) and they both are sorted before D
(distribution). So my guess is that if you add a database
at the distributor with a name that sorts
before "distribution" you will see the problem.
/Peter
>--Original Message--
>can either of you script out your tables, publications,
subscriptions so I[vbcol=seagreen]
>or MS folks can repro this?
>--
>Hilary Cotter
>Looking for a book on SQL Server replication?
>http://www.nwsu.com/0974973602.html
>
><ed.d@.osgbilling.com> wrote in message
>news:erbmp3eZEHA.3508@.TK2MSFTNGP09.phx.gbl...
fail, and whenever I[vbcol=seagreen]
aborting step failed"[vbcol=seagreen]
>error.
I saw that it was[vbcol=seagreen]
name from[vbcol=seagreen]
object name[vbcol=seagreen]
is in the[vbcol=seagreen]
>distribution
queue reader agent,[vbcol=seagreen]
>I
job was created by[vbcol=seagreen]
>the
the step to run with[vbcol=seagreen]
the agent. However,[vbcol=seagreen]
>the
job step, it's set to[vbcol=seagreen]
corrupted, but it sounds[vbcol=seagreen]
chime in![vbcol=seagreen]
in message[vbcol=seagreen]
that[vbcol=seagreen]
the[vbcol=seagreen]
this[vbcol=seagreen]
error[vbcol=seagreen]
scid=kb;EN-[vbcol=seagreen]
wrote[vbcol=seagreen]
sp3a.[vbcol=seagreen]
cluster[vbcol=seagreen]
failed"
>
>.
>
|||Hi,
I have the same problem but I achieved that the DB that appears in log would
be the correct one and continues wrong.
I have proved what you say and it continues without working.
"Peter Ericsson" <peter.l.ericsson@.ericsson.com> escribi en el mensaje
news:2ac8f01c467ed$c0c284a0$a501280a@.phx.gbl...[vbcol=seagreen]
> I don't think it has anything to do with our publications
> or subscriptions my guess is that both Ed and I have user
> database starting in my case with A (AMS) and in Ed's
> case B (Bill) and they both are sorted before D
> (distribution). So my guess is that if you add a database
> at the distributor with a name that sorts
> before "distribution" you will see the problem.
> /Peter
> subscriptions so I
> fail, and whenever I
> aborting step failed"
> I saw that it was
> name from
> object name
> is in the
> queue reader agent,
> job was created by
> the step to run with
> the agent. However,
> job step, it's set to
> corrupted, but it sounds
> chime in!
> in message
> that
> the
> this
> error
> scid=kb;EN-
> wrote
> sp3a.
> cluster
> failed"
Queue Reader failes occasionally
I am using transactional replication with queued updating between two
servers running SQL Server 2000 SP3. Publisher and distributor run
both on the same server (a cluster machine). There is one subscriber.
The Queue Reader Agent starts successfully, and transactions
originating on either side are properly replicated. However, the Queue
Reader occasionally fails. I enabled logging, but the output file
looks normal to me; several queries for queued data, but then it seems
to timeout. It just sits there for 3 minutes, then fails and retries.
No error message in the output file. Finally (after specified count of
retries) the agent shuts down with the message "Remote procedure call
failed". When I try to restart Queue Reader Agent, same behavior. I
have to examine the queue (table MSreplication_queue) and delete the
first respectively first few rows. Before deleting I execute the
corresponding queries (sp calls of type sp_MSsync_upd_MyTable) in
Query Analyzer, what succeeds. After deleting the rows from
MSreplication_queue I can restart Queue Reader Agent and now all works
properly up to the next fail. :-(
Does anyone have any advice? Thanks in advance
Dorrit
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/Replication-...ict237945.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=827170
How many subscribers do you have? Queued Replication works best with under
10 subscribers and when most of the transactions originate on your
publisher.
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
"Riemi" <UseLinkToEmail@.dbForumz.com> wrote in message
news:4_827170_8970ece2d43554af53766b469d92d042@.dbf orumz.com...
> I am using transactional replication with queued updating between two
> servers running SQL Server 2000 SP3. Publisher and distributor run
> both on the same server (a cluster machine). There is one subscriber.
> The Queue Reader Agent starts successfully, and transactions
> originating on either side are properly replicated. However, the Queue
> Reader occasionally fails. I enabled logging, but the output file
> looks normal to me; several queries for queued data, but then it seems
> to timeout. It just sits there for 3 minutes, then fails and retries.
> No error message in the output file. Finally (after specified count of
> retries) the agent shuts down with the message "Remote procedure call
> failed". When I try to restart Queue Reader Agent, same behavior. I
> have to examine the queue (table MSreplication_queue) and delete the
> first respectively first few rows. Before deleting I execute the
> corresponding queries (sp calls of type sp_MSsync_upd_MyTable) in
> Query Analyzer, what succeeds. After deleting the rows from
> MSreplication_queue I can restart Queue Reader Agent and now all works
> properly up to the next fail. :-(
> Does anyone have any advice? Thanks in advance
> Dorrit
> --
> Posted using the http://www.dbforumz.com interface, at author's request
> Articles individually checked for conformance to usenet standards
> Topic URL:
http://www.dbforumz.com/Replication-...ict237945.html
> Visit Topic URL to contact author (reg. req'd). Report abuse:
http://www.dbforumz.com/eform.php?p=827170
|||"" wrote:
> How many subscribers do you have? Queued Replication works
> best with under
> 10 subscribers and when most of the transactions originate on
> your
> publisher.
> --
> 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
> "Riemi" <UseLinkToEmail@.dbForumz.com> wrote in message
> news:4_827170_8970ece2d43554af53766b469d92d042@.dbf orumz.com...
> between two
> distributor run
> subscriber.
> the Queue
> file
> then it seems
> retries.
> specified count of
> procedure call
> behavior. I
> delete the
> the
> sp_MSsync_upd_MyTable) in
> all works
> standards
> http://www.dbforumz.com/Replication-...ict237945.html
> abuse:
> http://www.dbforumz.com/eform.php?p=827170
There is one subscriber.
servers running SQL Server 2000 SP3. Publisher and distributor run
both on the same server (a cluster machine). There is one subscriber.
The Queue Reader Agent starts successfully, and transactions
originating on either side are properly replicated. However, the Queue
Reader occasionally fails. I enabled logging, but the output file
looks normal to me; several queries for queued data, but then it seems
to timeout. It just sits there for 3 minutes, then fails and retries.
No error message in the output file. Finally (after specified count of
retries) the agent shuts down with the message "Remote procedure call
failed". When I try to restart Queue Reader Agent, same behavior. I
have to examine the queue (table MSreplication_queue) and delete the
first respectively first few rows. Before deleting I execute the
corresponding queries (sp calls of type sp_MSsync_upd_MyTable) in
Query Analyzer, what succeeds. After deleting the rows from
MSreplication_queue I can restart Queue Reader Agent and now all works
properly up to the next fail. :-(
Does anyone have any advice? Thanks in advance
Dorrit
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/Replication-...ict237945.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=827170
How many subscribers do you have? Queued Replication works best with under
10 subscribers and when most of the transactions originate on your
publisher.
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
"Riemi" <UseLinkToEmail@.dbForumz.com> wrote in message
news:4_827170_8970ece2d43554af53766b469d92d042@.dbf orumz.com...
> I am using transactional replication with queued updating between two
> servers running SQL Server 2000 SP3. Publisher and distributor run
> both on the same server (a cluster machine). There is one subscriber.
> The Queue Reader Agent starts successfully, and transactions
> originating on either side are properly replicated. However, the Queue
> Reader occasionally fails. I enabled logging, but the output file
> looks normal to me; several queries for queued data, but then it seems
> to timeout. It just sits there for 3 minutes, then fails and retries.
> No error message in the output file. Finally (after specified count of
> retries) the agent shuts down with the message "Remote procedure call
> failed". When I try to restart Queue Reader Agent, same behavior. I
> have to examine the queue (table MSreplication_queue) and delete the
> first respectively first few rows. Before deleting I execute the
> corresponding queries (sp calls of type sp_MSsync_upd_MyTable) in
> Query Analyzer, what succeeds. After deleting the rows from
> MSreplication_queue I can restart Queue Reader Agent and now all works
> properly up to the next fail. :-(
> Does anyone have any advice? Thanks in advance
> Dorrit
> --
> Posted using the http://www.dbforumz.com interface, at author's request
> Articles individually checked for conformance to usenet standards
> Topic URL:
http://www.dbforumz.com/Replication-...ict237945.html
> Visit Topic URL to contact author (reg. req'd). Report abuse:
http://www.dbforumz.com/eform.php?p=827170
|||"" wrote:
> How many subscribers do you have? Queued Replication works
> best with under
> 10 subscribers and when most of the transactions originate on
> your
> publisher.
> --
> 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
> "Riemi" <UseLinkToEmail@.dbForumz.com> wrote in message
> news:4_827170_8970ece2d43554af53766b469d92d042@.dbf orumz.com...
> between two
> distributor run
> subscriber.
> the Queue
> file
> then it seems
> retries.
> specified count of
> procedure call
> behavior. I
> delete the
> the
> sp_MSsync_upd_MyTable) in
> all works
> standards
> http://www.dbforumz.com/Replication-...ict237945.html
> abuse:
> http://www.dbforumz.com/eform.php?p=827170
There is one subscriber.
Queue Reader Agent will not start after network failure
Senario
I have a SQL Server 2000 SP4 that is Publisher and distributor plus a SQL
Server 2000 SP3a that is a Publisher.
There replicates to 2 SQL Server 2000 SP4 over the company WAN.
After a roughly 10 min long network failure the queue reader agent will not
start.
Any ideas?
Thanks.
Jonas
Can you enable logging and post the message you get back here?
http://support.microsoft.com/kb/312292
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
"Jonas Larsen" <JonasLarsen@.discussions.microsoft.com> wrote in message
news:7F7768C5-1FF1-4947-8318-9F9A3581A9A3@.microsoft.com...
> Senario
> I have a SQL Server 2000 SP4 that is Publisher and distributor plus a SQL
> Server 2000 SP3a that is a Publisher.
> There replicates to 2 SQL Server 2000 SP4 over the company WAN.
> After a roughly 10 min long network failure the queue reader agent will
> not
> start.
> Any ideas?
> Thanks.
> Jonas
I have a SQL Server 2000 SP4 that is Publisher and distributor plus a SQL
Server 2000 SP3a that is a Publisher.
There replicates to 2 SQL Server 2000 SP4 over the company WAN.
After a roughly 10 min long network failure the queue reader agent will not
start.
Any ideas?
Thanks.
Jonas
Can you enable logging and post the message you get back here?
http://support.microsoft.com/kb/312292
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
"Jonas Larsen" <JonasLarsen@.discussions.microsoft.com> wrote in message
news:7F7768C5-1FF1-4947-8318-9F9A3581A9A3@.microsoft.com...
> Senario
> I have a SQL Server 2000 SP4 that is Publisher and distributor plus a SQL
> Server 2000 SP3a that is a Publisher.
> There replicates to 2 SQL Server 2000 SP4 over the company WAN.
> After a roughly 10 min long network failure the queue reader agent will
> not
> start.
> Any ideas?
> Thanks.
> Jonas
Monday, February 20, 2012
Queue Reader Aborting. The step failed
Hi All,
I have a bi-directional transactional replication between two SQL Server
2000 box. After I reboot the publisher, the Queue Reader kept failing, saying
"Queue Reader Aborting, the step failed, error 14151, state 1". And the worse
thing was that I could not restart it. It would fail immediately after I
restarted it. Any thought?
Thanks in advance,
Hi Franky,
It sounds like SQL Server Agent couldn't start the queue reader job. Could
you also check the job history at Management\SQL Server Agent\jobs\Queue
Reader job\View job history ro see if there is any information there?
Jing Sun
Microsoft SQL server Replication team
"Franky" <Franky@.discussions.microsoft.com> wrote in message
news:C9E1AB14-52EA-4B90-950F-5D2844BDBFC3@.microsoft.com...
> Hi All,
> I have a bi-directional transactional replication between two SQL Server
> 2000 box. After I reboot the publisher, the Queue Reader kept failing,
> saying
> "Queue Reader Aborting, the step failed, error 14151, state 1". And the
> worse
> thing was that I could not restart it. It would fail immediately after I
> restarted it. Any thought?
> Thanks in advance,
I have a bi-directional transactional replication between two SQL Server
2000 box. After I reboot the publisher, the Queue Reader kept failing, saying
"Queue Reader Aborting, the step failed, error 14151, state 1". And the worse
thing was that I could not restart it. It would fail immediately after I
restarted it. Any thought?
Thanks in advance,
Hi Franky,
It sounds like SQL Server Agent couldn't start the queue reader job. Could
you also check the job history at Management\SQL Server Agent\jobs\Queue
Reader job\View job history ro see if there is any information there?
Jing Sun
Microsoft SQL server Replication team
"Franky" <Franky@.discussions.microsoft.com> wrote in message
news:C9E1AB14-52EA-4B90-950F-5D2844BDBFC3@.microsoft.com...
> Hi All,
> I have a bi-directional transactional replication between two SQL Server
> 2000 box. After I reboot the publisher, the Queue Reader kept failing,
> saying
> "Queue Reader Aborting, the step failed, error 14151, state 1". And the
> worse
> thing was that I could not restart it. It would fail immediately after I
> restarted it. Any thought?
> Thanks in advance,
Queue Reader Aborting
I am using transactional replication with queued updating. The publisher is
located in Norway, with a single subscriber in the US. The Queue Reader
Agent starts successfully, and transactions originating on either side are
properly replicated. However, the Queue Reader agent eventually shuts down
with the message "Queue Reader Aborting". When I check the session log, I
find occurrences of the error: "Server does not exist or access denied"
throughout the entire time the agent runs. Right before the agent actually
shuts down, I get the following three errors:
"Communication link failure"
"Error in caching messages from SQL Queue"
"Queue Reader Aborting"
After the queue reader fails, it does not restart automatically - it
requires manual intervention. We are aware that the connection is sporatic,
and can accept the queue building up on both sides. Is the communication
link failure a result of some threshold being reached? Can that threshold be
removed? If not, is there a way to have the queue reader agent automatically
restart?
Hi,
I have a replication scenario similar to yours, and my queue reader also
periodically aborts with those same messages. I don't have any insight yet
as to what causes it, but at least now you know you're not alone!
Ed
"Daniel Inman" <DanielInman@.discussions.microsoft.com> wrote in message
news:914EA9FB-1554-45F7-BC84-47E44D56161C@.microsoft.com...
> I am using transactional replication with queued updating. The publisher
is
> located in Norway, with a single subscriber in the US. The Queue Reader
> Agent starts successfully, and transactions originating on either side are
> properly replicated. However, the Queue Reader agent eventually shuts
down
> with the message "Queue Reader Aborting". When I check the session log, I
> find occurrences of the error: "Server does not exist or access denied"
> throughout the entire time the agent runs. Right before the agent
actually
> shuts down, I get the following three errors:
> "Communication link failure"
> "Error in caching messages from SQL Queue"
> "Queue Reader Aborting"
> After the queue reader fails, it does not restart automatically - it
> requires manual intervention. We are aware that the connection is
sporatic,
> and can accept the queue building up on both sides. Is the communication
> link failure a result of some threshold being reached? Can that threshold
be
> removed? If not, is there a way to have the queue reader agent
automatically
> restart?
located in Norway, with a single subscriber in the US. The Queue Reader
Agent starts successfully, and transactions originating on either side are
properly replicated. However, the Queue Reader agent eventually shuts down
with the message "Queue Reader Aborting". When I check the session log, I
find occurrences of the error: "Server does not exist or access denied"
throughout the entire time the agent runs. Right before the agent actually
shuts down, I get the following three errors:
"Communication link failure"
"Error in caching messages from SQL Queue"
"Queue Reader Aborting"
After the queue reader fails, it does not restart automatically - it
requires manual intervention. We are aware that the connection is sporatic,
and can accept the queue building up on both sides. Is the communication
link failure a result of some threshold being reached? Can that threshold be
removed? If not, is there a way to have the queue reader agent automatically
restart?
Hi,
I have a replication scenario similar to yours, and my queue reader also
periodically aborts with those same messages. I don't have any insight yet
as to what causes it, but at least now you know you're not alone!
Ed
"Daniel Inman" <DanielInman@.discussions.microsoft.com> wrote in message
news:914EA9FB-1554-45F7-BC84-47E44D56161C@.microsoft.com...
> I am using transactional replication with queued updating. The publisher
is
> located in Norway, with a single subscriber in the US. The Queue Reader
> Agent starts successfully, and transactions originating on either side are
> properly replicated. However, the Queue Reader agent eventually shuts
down
> with the message "Queue Reader Aborting". When I check the session log, I
> find occurrences of the error: "Server does not exist or access denied"
> throughout the entire time the agent runs. Right before the agent
actually
> shuts down, I get the following three errors:
> "Communication link failure"
> "Error in caching messages from SQL Queue"
> "Queue Reader Aborting"
> After the queue reader fails, it does not restart automatically - it
> requires manual intervention. We are aware that the connection is
sporatic,
> and can accept the queue building up on both sides. Is the communication
> link failure a result of some threshold being reached? Can that threshold
be
> removed? If not, is there a way to have the queue reader agent
automatically
> restart?
Queue Reader - remote procedure call failed
I am running transactional repl with an updateable subscription between two servers running SQL Server 2000 SP3, all agents running on the publisher. Every now and then, the Queue reader fails. I enable logging and attempt restart. The output file looks n
ormal to me; several queries for queued data, but then it seems to timeout. It just sits there for 3 minutes, then fails and retries. I can successfully query the other server, so I know it's not a communications problem. The event viewer simply says "the
remote procedure call failed and did not execute". I can't find any other error messages.
Does anyone have any advice? Thank you.
Microsoft SQL Server Replication Queue Reader Agent 8.00.760
Copyright (c) 2000 Microsoft Corporation
Microsoft SQL Server Replication Agent: [MIALDCS-1].9
Trying to Connect to Local Distributor
Connecting to QueueReader 'MIALDCS-1.distribution'
Server: MIALDCS-1
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[7/30/2004 12:17:19 PM]MIALDCS-1.distribution: select count(*) from master.dbo.sysprocesses where [program_name] = 'Queue Reader Main (distribution)'
[7/30/2004 12:17:19 PM]MIALDCS-1.distribution: select top 1 id, name from MSqreader_agents
[7/30/2004 12:17:19 PM]MIALDCS-1.distribution: select SERVERPROPERTY('IsClustered')
Queue Reader Agent [MIALDCS-1].9 (Id = 3) started
Repl Agent Status: 1
[7/30/2004 12:17:19 PM]MIALDCS-1.distribution: execute dbo.sp_MShelp_profile 3, 9, N''
Opening SQL based queues
[7/30/2004 12:17:19 PM]MIALDCS-1.distribution: exec master.dbo.sp_MSenum_replsqlqueues N'distribution'
Worker Thread 608 : Starting
The Message Queuing service does not exist[7/30/2004 12:17:19 PM]MIALDCS-1.distribution: exec dbo.sp_MShelp_subscriber_info N'MIALDCS-1', N'MIALDCS-2'
The Message Queuing service is not available
Connecting to MIALDCS-2 'MIALDCS-2.Island'
Server: MIALDCS-2
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
MIALDCS-2.Island: {? = call dbo.sp_getsqlqueueversion (?, ?, ?, ?)}
MIALDCS-2.Island: {? = call dbo.sp_replsqlqgetrows (N'MIALDCS-1', N'Island', N'Island')}
[7/30/2004 12:17:31 PM]MIALDCS-1.distribution: exec dbo.sp_helpdistpublisher @.publisher = N'MIALDCS-1'
Connecting to MIALDCS-1 'MIALDCS-1.Island'
Worker Thread 608 : processing Transaction [2LhOShgh_agC2<T]?LH.h-5--09M--] of [SQL Queue]
Worker Thread 608 : Started Queue Transaction
Worker Thread 608 : Started SQL Tran
MIALDCS-1.Island: {? = call dbo.sp_getqueuedarticlesynctraninfo (N'Island', 44)}
SQL Command : <exec [dbo].[sp_MSsync_del_Leg_Seat_Map_1] N'MIALDCS-2', N'Island', 'F2', '101', '2004-07-24 00:00:00.000', '1', 0, 0, ' ', ' ', 0, 12632256, ' ', 'Y', ' ', 'N', 'N', '', '', '', '', '', '', '', '', ' ', ' ', ' ', 'E8613112-29DC-4563-B3F0-58
665C4967B9', 1>
(hundreds more records follow)
what command is it failing on?
The problem is probably related to the execution of a single proc, which is
locking on the publisher.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"LeeH" <LeeH@.discussions.microsoft.com> wrote in message
news:055E41A9-DA76-403B-8D93-3F4C32BA6DDE@.microsoft.com...
> I am running transactional repl with an updateable subscription between
two servers running SQL Server 2000 SP3, all agents running on the
publisher. Every now and then, the Queue reader fails. I enable logging and
attempt restart. The output file looks normal to me; several queries for
queued data, but then it seems to timeout. It just sits there for 3 minutes,
then fails and retries. I can successfully query the other server, so I know
it's not a communications problem. The event viewer simply says "the remote
procedure call failed and did not execute". I can't find any other error
messages.
> Does anyone have any advice? Thank you.
> Microsoft SQL Server Replication Queue Reader Agent 8.00.760
> Copyright (c) 2000 Microsoft Corporation
> Microsoft SQL Server Replication Agent: [MIALDCS-1].9
> Trying to Connect to Local Distributor
> Connecting to QueueReader 'MIALDCS-1.distribution'
> Server: MIALDCS-1
> DBMS: Microsoft SQL Server
> Version: 08.00.0760
> user name: dbo
> API conformance: 2
> SQL conformance: 1
> transaction capable: 2
> read only: N
> identifier quote char: "
> non_nullable_columns: 1
> owner usage: 31
> max table name len: 128
> max column name len: 128
> need long data len: Y
> max columns in table: 1024
> max columns in index: 16
> max char literal len: 524288
> max statement len: 524288
> max row size: 524288
> [7/30/2004 12:17:19 PM]MIALDCS-1.distribution: select count(*) from
master.dbo.sysprocesses where [program_name] = 'Queue Reader Main
(distribution)'
> [7/30/2004 12:17:19 PM]MIALDCS-1.distribution: select top 1 id, name from
MSqreader_agents
> [7/30/2004 12:17:19 PM]MIALDCS-1.distribution: select
SERVERPROPERTY('IsClustered')
> Queue Reader Agent [MIALDCS-1].9 (Id = 3) started
> Repl Agent Status: 1
> [7/30/2004 12:17:19 PM]MIALDCS-1.distribution: execute
dbo.sp_MShelp_profile 3, 9, N''
> Opening SQL based queues
> [7/30/2004 12:17:19 PM]MIALDCS-1.distribution: exec
master.dbo.sp_MSenum_replsqlqueues N'distribution'
> Worker Thread 608 : Starting
> The Message Queuing service does not exist[7/30/2004 12:17:19
PM]MIALDCS-1.distribution: exec dbo.sp_MShelp_subscriber_info N'MIALDCS-1',
N'MIALDCS-2'
> The Message Queuing service is not available
> Connecting to MIALDCS-2 'MIALDCS-2.Island'
> Server: MIALDCS-2
> DBMS: Microsoft SQL Server
> Version: 08.00.0760
> user name: dbo
> API conformance: 2
> SQL conformance: 1
> transaction capable: 2
> read only: N
> identifier quote char: "
> non_nullable_columns: 1
> owner usage: 31
> max table name len: 128
> max column name len: 128
> need long data len: Y
> max columns in table: 1024
> max columns in index: 16
> max char literal len: 524288
> max statement len: 524288
> max row size: 524288
> MIALDCS-2.Island: {? = call dbo.sp_getsqlqueueversion (?, ?, ?, ?)}
> MIALDCS-2.Island: {? = call dbo.sp_replsqlqgetrows (N'MIALDCS-1',
N'Island', N'Island')}
> [7/30/2004 12:17:31 PM]MIALDCS-1.distribution: exec
dbo.sp_helpdistpublisher @.publisher = N'MIALDCS-1'
> Connecting to MIALDCS-1 'MIALDCS-1.Island'
> Worker Thread 608 : processing Transaction
[2LhOShgh_agC2<T]?LH.h-5--09M--] of [SQL Queue]
> Worker Thread 608 : Started Queue Transaction
> Worker Thread 608 : Started SQL Tran
> MIALDCS-1.Island: {? = call dbo.sp_getqueuedarticlesynctraninfo
(N'Island', 44)}
> SQL Command : <exec [dbo].[sp_MSsync_del_Leg_Seat_Map_1] N'MIALDCS-2',
N'Island', 'F2', '101', '2004-07-24 00:00:00.000', '1', 0, 0, ' ', ' ', 0,
12632256, ' ', 'Y', ' ', 'N', 'N', '', '', '', '', '', '', '', '', ' ', ' ',
' ', 'E8613112-29DC-4563-B3F0-58665C4967B9', 1>
> (hundreds more records follow)
>
|||I don't see anything in the log that indicates failure. It seems that the
agent just restarts. The event viewer simply says "the remote procedure call
failed and did not execute".
"Hilary Cotter" wrote:
> what command is it failing on?
> The problem is probably related to the execution of a single proc, which is
> locking on the publisher.
>
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "LeeH" <LeeH@.discussions.microsoft.com> wrote in message
> news:055E41A9-DA76-403B-8D93-3F4C32BA6DDE@.microsoft.com...
> two servers running SQL Server 2000 SP3, all agents running on the
> publisher. Every now and then, the Queue reader fails. I enable logging and
> attempt restart. The output file looks normal to me; several queries for
> queued data, but then it seems to timeout. It just sits there for 3 minutes,
> then fails and retries. I can successfully query the other server, so I know
> it's not a communications problem. The event viewer simply says "the remote
> procedure call failed and did not execute". I can't find any other error
> messages.
> master.dbo.sysprocesses where [program_name] = 'Queue Reader Main
> (distribution)'
> MSqreader_agents
> SERVERPROPERTY('IsClustered')
> dbo.sp_MShelp_profile 3, 9, N''
> master.dbo.sp_MSenum_replsqlqueues N'distribution'
> PM]MIALDCS-1.distribution: exec dbo.sp_MShelp_subscriber_info N'MIALDCS-1',
> N'MIALDCS-2'
> N'Island', N'Island')}
> dbo.sp_helpdistpublisher @.publisher = N'MIALDCS-1'
> [2LhOShgh_agC2<T]?LH.h-5--09M--] of [SQL Queue]
> (N'Island', 44)}
> N'Island', 'F2', '101', '2004-07-24 00:00:00.000', '1', 0, 0, ' ', ' ', 0,
> 12632256, ' ', 'Y', ' ', 'N', 'N', '', '', '', '', '', '', '', '', ' ', ' ',
> ' ', 'E8613112-29DC-4563-B3F0-58665C4967B9', 1>
>
>
ormal to me; several queries for queued data, but then it seems to timeout. It just sits there for 3 minutes, then fails and retries. I can successfully query the other server, so I know it's not a communications problem. The event viewer simply says "the
remote procedure call failed and did not execute". I can't find any other error messages.
Does anyone have any advice? Thank you.
Microsoft SQL Server Replication Queue Reader Agent 8.00.760
Copyright (c) 2000 Microsoft Corporation
Microsoft SQL Server Replication Agent: [MIALDCS-1].9
Trying to Connect to Local Distributor
Connecting to QueueReader 'MIALDCS-1.distribution'
Server: MIALDCS-1
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[7/30/2004 12:17:19 PM]MIALDCS-1.distribution: select count(*) from master.dbo.sysprocesses where [program_name] = 'Queue Reader Main (distribution)'
[7/30/2004 12:17:19 PM]MIALDCS-1.distribution: select top 1 id, name from MSqreader_agents
[7/30/2004 12:17:19 PM]MIALDCS-1.distribution: select SERVERPROPERTY('IsClustered')
Queue Reader Agent [MIALDCS-1].9 (Id = 3) started
Repl Agent Status: 1
[7/30/2004 12:17:19 PM]MIALDCS-1.distribution: execute dbo.sp_MShelp_profile 3, 9, N''
Opening SQL based queues
[7/30/2004 12:17:19 PM]MIALDCS-1.distribution: exec master.dbo.sp_MSenum_replsqlqueues N'distribution'
Worker Thread 608 : Starting
The Message Queuing service does not exist[7/30/2004 12:17:19 PM]MIALDCS-1.distribution: exec dbo.sp_MShelp_subscriber_info N'MIALDCS-1', N'MIALDCS-2'
The Message Queuing service is not available
Connecting to MIALDCS-2 'MIALDCS-2.Island'
Server: MIALDCS-2
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
MIALDCS-2.Island: {? = call dbo.sp_getsqlqueueversion (?, ?, ?, ?)}
MIALDCS-2.Island: {? = call dbo.sp_replsqlqgetrows (N'MIALDCS-1', N'Island', N'Island')}
[7/30/2004 12:17:31 PM]MIALDCS-1.distribution: exec dbo.sp_helpdistpublisher @.publisher = N'MIALDCS-1'
Connecting to MIALDCS-1 'MIALDCS-1.Island'
Worker Thread 608 : processing Transaction [2LhOShgh_agC2<T]?LH.h-5--09M--] of [SQL Queue]
Worker Thread 608 : Started Queue Transaction
Worker Thread 608 : Started SQL Tran
MIALDCS-1.Island: {? = call dbo.sp_getqueuedarticlesynctraninfo (N'Island', 44)}
SQL Command : <exec [dbo].[sp_MSsync_del_Leg_Seat_Map_1] N'MIALDCS-2', N'Island', 'F2', '101', '2004-07-24 00:00:00.000', '1', 0, 0, ' ', ' ', 0, 12632256, ' ', 'Y', ' ', 'N', 'N', '', '', '', '', '', '', '', '', ' ', ' ', ' ', 'E8613112-29DC-4563-B3F0-58
665C4967B9', 1>
(hundreds more records follow)
what command is it failing on?
The problem is probably related to the execution of a single proc, which is
locking on the publisher.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"LeeH" <LeeH@.discussions.microsoft.com> wrote in message
news:055E41A9-DA76-403B-8D93-3F4C32BA6DDE@.microsoft.com...
> I am running transactional repl with an updateable subscription between
two servers running SQL Server 2000 SP3, all agents running on the
publisher. Every now and then, the Queue reader fails. I enable logging and
attempt restart. The output file looks normal to me; several queries for
queued data, but then it seems to timeout. It just sits there for 3 minutes,
then fails and retries. I can successfully query the other server, so I know
it's not a communications problem. The event viewer simply says "the remote
procedure call failed and did not execute". I can't find any other error
messages.
> Does anyone have any advice? Thank you.
> Microsoft SQL Server Replication Queue Reader Agent 8.00.760
> Copyright (c) 2000 Microsoft Corporation
> Microsoft SQL Server Replication Agent: [MIALDCS-1].9
> Trying to Connect to Local Distributor
> Connecting to QueueReader 'MIALDCS-1.distribution'
> Server: MIALDCS-1
> DBMS: Microsoft SQL Server
> Version: 08.00.0760
> user name: dbo
> API conformance: 2
> SQL conformance: 1
> transaction capable: 2
> read only: N
> identifier quote char: "
> non_nullable_columns: 1
> owner usage: 31
> max table name len: 128
> max column name len: 128
> need long data len: Y
> max columns in table: 1024
> max columns in index: 16
> max char literal len: 524288
> max statement len: 524288
> max row size: 524288
> [7/30/2004 12:17:19 PM]MIALDCS-1.distribution: select count(*) from
master.dbo.sysprocesses where [program_name] = 'Queue Reader Main
(distribution)'
> [7/30/2004 12:17:19 PM]MIALDCS-1.distribution: select top 1 id, name from
MSqreader_agents
> [7/30/2004 12:17:19 PM]MIALDCS-1.distribution: select
SERVERPROPERTY('IsClustered')
> Queue Reader Agent [MIALDCS-1].9 (Id = 3) started
> Repl Agent Status: 1
> [7/30/2004 12:17:19 PM]MIALDCS-1.distribution: execute
dbo.sp_MShelp_profile 3, 9, N''
> Opening SQL based queues
> [7/30/2004 12:17:19 PM]MIALDCS-1.distribution: exec
master.dbo.sp_MSenum_replsqlqueues N'distribution'
> Worker Thread 608 : Starting
> The Message Queuing service does not exist[7/30/2004 12:17:19
PM]MIALDCS-1.distribution: exec dbo.sp_MShelp_subscriber_info N'MIALDCS-1',
N'MIALDCS-2'
> The Message Queuing service is not available
> Connecting to MIALDCS-2 'MIALDCS-2.Island'
> Server: MIALDCS-2
> DBMS: Microsoft SQL Server
> Version: 08.00.0760
> user name: dbo
> API conformance: 2
> SQL conformance: 1
> transaction capable: 2
> read only: N
> identifier quote char: "
> non_nullable_columns: 1
> owner usage: 31
> max table name len: 128
> max column name len: 128
> need long data len: Y
> max columns in table: 1024
> max columns in index: 16
> max char literal len: 524288
> max statement len: 524288
> max row size: 524288
> MIALDCS-2.Island: {? = call dbo.sp_getsqlqueueversion (?, ?, ?, ?)}
> MIALDCS-2.Island: {? = call dbo.sp_replsqlqgetrows (N'MIALDCS-1',
N'Island', N'Island')}
> [7/30/2004 12:17:31 PM]MIALDCS-1.distribution: exec
dbo.sp_helpdistpublisher @.publisher = N'MIALDCS-1'
> Connecting to MIALDCS-1 'MIALDCS-1.Island'
> Worker Thread 608 : processing Transaction
[2LhOShgh_agC2<T]?LH.h-5--09M--] of [SQL Queue]
> Worker Thread 608 : Started Queue Transaction
> Worker Thread 608 : Started SQL Tran
> MIALDCS-1.Island: {? = call dbo.sp_getqueuedarticlesynctraninfo
(N'Island', 44)}
> SQL Command : <exec [dbo].[sp_MSsync_del_Leg_Seat_Map_1] N'MIALDCS-2',
N'Island', 'F2', '101', '2004-07-24 00:00:00.000', '1', 0, 0, ' ', ' ', 0,
12632256, ' ', 'Y', ' ', 'N', 'N', '', '', '', '', '', '', '', '', ' ', ' ',
' ', 'E8613112-29DC-4563-B3F0-58665C4967B9', 1>
> (hundreds more records follow)
>
|||I don't see anything in the log that indicates failure. It seems that the
agent just restarts. The event viewer simply says "the remote procedure call
failed and did not execute".
"Hilary Cotter" wrote:
> what command is it failing on?
> The problem is probably related to the execution of a single proc, which is
> locking on the publisher.
>
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "LeeH" <LeeH@.discussions.microsoft.com> wrote in message
> news:055E41A9-DA76-403B-8D93-3F4C32BA6DDE@.microsoft.com...
> two servers running SQL Server 2000 SP3, all agents running on the
> publisher. Every now and then, the Queue reader fails. I enable logging and
> attempt restart. The output file looks normal to me; several queries for
> queued data, but then it seems to timeout. It just sits there for 3 minutes,
> then fails and retries. I can successfully query the other server, so I know
> it's not a communications problem. The event viewer simply says "the remote
> procedure call failed and did not execute". I can't find any other error
> messages.
> master.dbo.sysprocesses where [program_name] = 'Queue Reader Main
> (distribution)'
> MSqreader_agents
> SERVERPROPERTY('IsClustered')
> dbo.sp_MShelp_profile 3, 9, N''
> master.dbo.sp_MSenum_replsqlqueues N'distribution'
> PM]MIALDCS-1.distribution: exec dbo.sp_MShelp_subscriber_info N'MIALDCS-1',
> N'MIALDCS-2'
> N'Island', N'Island')}
> dbo.sp_helpdistpublisher @.publisher = N'MIALDCS-1'
> [2LhOShgh_agC2<T]?LH.h-5--09M--] of [SQL Queue]
> (N'Island', 44)}
> N'Island', 'F2', '101', '2004-07-24 00:00:00.000', '1', 0, 0, ' ', ' ', 0,
> 12632256, ' ', 'Y', ' ', 'N', 'N', '', '', '', '', '', '', '', '', ' ', ' ',
> ' ', 'E8613112-29DC-4563-B3F0-58665C4967B9', 1>
>
>
Subscribe to:
Posts (Atom)