Guys,
I get the error message: Queue reader aborting. The steps failed...in queue reader agents of SQL 2000 replication. Could any one give me a hint of how to troubleshoot this problem? Thanks a lot in advance.
I have just started getting the following error from the queue reader but I can't see why.
The Queue Reader Agent has encountered the error ''Row handle is invalid.'' when connecting to ''Database'' on ''ServerName''. Ensure that the publication and subscription are defined properly and that both servers are running.
Does anybody know what it means / how I can fix the problem?
Thanks
This is the most frustrating error I've ever encountered.
I can get no more info from sql server besides "server execution failed". It gives this error when starting the snapshot, distribution, and log reader agents.
This server has been working in this configuration for over a year. We made no changes except restarted the server. This is a windows 2003 server with sql server 2000 running all the latest patches and windows updates.
Hi
By default, the Log Reader Agent and Distribution Agent use the account the starts SQL Server Agent to logon into the Publisher SQL Server when performing SQL Server Transactional replication. Is there a way of configuring them so they use a different account.
Thanks
Tariq
Hello,
by mistake I added an queue reader agent to a distribution database. But how can I drop the queue agent. There isn't a stored proc 'sp_dropqreader_agent'.
The only way I found (apart from dropping the whole distribution databse) is to stop and disable the generated job!
Is there any other way to get rid of the queue reader agent ?
Wolfgang
I'm creating an app that could potentially send thousands of messages to a Service Broker queue in a matter of seconds. The stored proc that reads records off the queue will call a remote stored procedure for each message to save data in a remote database.
My question is this. How does Service Broker determine how quickly to read those messages off the queue it was sent to? Ideally I'd like Service Broker to do most of its work when SQL Server isn't busy doing other things instead of contending with SQL Server while it's doing perhaps more important work. Is there any way to control this? How does this work?
Thanks very much - Amos
I've set up transactional replication with queued updating. If I stop the queue reader agent through EM, I can't start it again. Agent's history says "Queue Reader aborting. The step failed."
All servers are SQL Server 2000 SP3.
Help is greatly appreciated!
Which replication agents are available in Express edition?
abdul
Is it possible to obtain the status, running or not running, of the distribution agents that appear under Replication MonitorAgentsDistribution Agents in Enterprise Manager using WMI? If yes, can you please tell me the WMI namespace and objects that can be used?
Thanks
Hi all replication experts...
I have created transactional replication on my servers (production server and backup server) and created pull subscription at the backup server. On my production server that also is a distributor and publisher there exist two agents, Snapshot agent and Log reader in the Publishers folder under replication Monitor.
There are also another agent with the backup server name and database name: BackServ:DBName having the type Pull and 'No replicated transactions are available' as Last Action. My question is: What is this? Must it be there?
Grateful for answers
Best,
/M
Hi all,
I have encountered some problems in scheduling the replication agents but before explaining that let me give a small background of the current DB landscape.
It is implemented as Star topology or re-publisher topology with the publisher and the subscriber configured on SQL Server 2000 and it consists of ShareDB on SQL Server 2005 which acts as the re-publisher of data. All other databases publish articles to ShareDB which in turn re-publishes to the databases which subscribes for the article. The topology is using a remote distributor on SQL Server 2005 to offload the replication agent jobs processing from the OLTP environment. The distributor job agent is running continuously and it is shared for a specific database in case of Snapshot replication. And all the Transactional publications will have a separate distributor agent. At present the replications agents are scheduled on both the publisher and ShareDB.
Now, I am trying to enforce one way scheduling on the publisher side which will start the snapshot agent for the corresponding publication on ShareDB after the distributor agent transfers the data from the snapshot folder to the subscribers. This approach is effective and more reliable in the sense that data synchronization will happen immediately after the distributor agent transfers the data to ShareDB.
The following are the issues related to scheduling the replication agent on the publisher only.
1. I have included sp_start_job stored procedure after the run agent step in the shared distributor job agent steps to start the snapshot agent of the corresponding publication on ShareDB after the distributor agent transfers the data to ShareDB. Since the distributor agent is running continuously in our scenario, the next step is not getting executed and so the job agent of the corresponding publication on ShareDB is not started.
2. It is also necessary to specify either the job name or the job id in sp_start_job stored procedure to start the job agent. With the replication setup configured in the aforementioned fashion, how is it possible to invoke the snapshot job agent of the corresponding publication on the second link (from ShareDB) dynamically after the distributor agent on the first link is completed?
Thanks a lot,
Barry
Hi all,
I have encountered some problems in scheduling the replication agents but before explaining that let me give a small background of the current DB landscape.
It is implemented as Star topology or re-publisher topology with the publisher and the subscriber configured on SQL Server 2000 and it consists of ShareDB on SQL Server 2005 which acts as the re-publisher of data. All other databases publish articles to ShareDB which in turn re-publishes to the databases which subscribes for the article. The topology is using a remote distributor on SQL Server 2005 to offload the replication agent jobs processing from the OLTP environment. The distributor job agent is running continuously and it is shared for a specific database in case of Snapshot replication. And all the Transactional publications will have a separate distributor agent. At present the replications agents are scheduled on both the publisher and ShareDB.
Now, I am trying to enforce one way scheduling on the publisher side which will start the snapshot agent for the corresponding publication on ShareDB after the distributor agent transfers the data from the snapshot folder to the subscribers. This approach is effective and more reliable in the sense that data synchronization will happen immediately after the distributor agent transfers the data to ShareDB.
The following are the issues related to scheduling the replication agent on the publisher only.
1. I have included sp_start_job stored procedure after the run agent step in the shared distributor job agent steps to start the snapshot agent of the corresponding publication on ShareDB after the distributor agent transfers the data to ShareDB. Since the distributor agent is running continuously in our scenario, the next step is not getting executed and so the job agent of the corresponding publication on ShareDB is not started.
2. It is also necessary to specify either the job name or the job id in sp_start_job stored procedure to start the job agent. With the replication setup configured in the aforementioned fashion, how is it possible to invoke the snapshot job agent of the corresponding publication on the second link (from ShareDB) dynamically after the distributor agent on the first link is completed?
Thanks a lot,
Barry
Can I have multiple SQL Server Agents running concurrently?
I would like to have 10 instances of database running on my local SQL Server - each of these are Merge Replication to a unique database located offsite.
Do they all run through one SQL Server Agent?
If they do would this be a slow process as, I think, the SQL Server Agent queues the replications pending.
Thanks,
Bianca
Hi all,
I have 5 laptops, 1 laptop with SQL Server 2000 and the other 4 with SQL Server MSDE Rel A. I had a "Merge Replication" scheme that I had to set up. The laptop with 2000 will be the main Publisher/distributor and publishes/merges to two of the MSDE laptops(these laptops have pull subscriptions that pull from the 2000 laptop). These 2 MSDE laptops in turn Re-publish their data to the last 2 laptops and my scheme ends there. I have succesfully been able to Script this entire "Merge Replication" scheme, and I ran it. It creates all Components needed for my scheme to work.
1 issue though. It doesnt kick start the agents :eek: , so the replication scheme is just dormant.
Does anyone have any examples of scripts that I could use to kick off all the agents (snapshot,merge agents etc) and maybe to stop them too. I seriously need this, because we will need to have the entire process scripted.
Thanks
'Wale
p.s.
Also does anyone have sample scripts to alter settings for the SQL SERVER AGENT, for example altering its "Log On" parameter to allow a particular user with Admin privileges and also to switch its start mode to "Automatic"
During an upgrade, we had problems with PK errors on sp_MSget_repl_commands. I recreated the SP with the varbinary characteristic as we have seen in many posts and it was sucessful in starting. However, the sp now appears as a 'user' sp, not a 'system' sp, however I an not sure that is a real issue.
The problem I am seeing is that none of the agents are appearing in replication monitor under their agent folders. Replication appears to be working fine and the publications are showing on the distributor in the Replication Monitor - Publication folder under the appropriate publisher. But all of the agent specific folders (i.e. Snapshot agent, distribution agent, etc) are empty.
Another interesting point, is that if replication fails, the failure 'X' shows up on the Agents folder as well as the publisher, but again, no sign of the agent in the folder.
Thoughts?
Hi,
View 6 Replies View RelatedHi all
I have SQL 2000 SP4 running on a W2K server SP4.
The server acts as a subcriber and publisher and acts as its own distributor.
Each publication uses its own distribution agent and is set to loop.
There are 4 queued updating subscriptions on this box.
When running 3rd party tools to determine open active ports, the replication executables (distrib and logreader) are connecting to local server multiple times. Easily 15+ established connections plus many more waits.
Everything connects to 1433 then opens a new port.
Is this because of the queued updating?
Replication is functioning as it should without any problems.
I even checked our 2 main distribution servers which only act as distributors and there is not one connection from replication to itself.
Any insight would be appreciated
Thx
John
Hi guys,
I don't know why on earth. It only inform this: [WMI Data Reader Task] Error: An error occurred with the following error message: "Invalid query ".
My query is a simple one, very well-know for all of us:
strComputer = "SRVDESASQL2005"
Set wbemServices = GetObject("winmgmts:\" & strComputer)
Set wbemObjectSet = wbemServices.InstancesOf("Win32_LogicalMemoryConfiguration")
For Each wbemObject In wbemObjectSet
WScript.Echo "Total Physical Memory (kb): " & wbemObject.TotalPhysicalMemory
Next
Let me know any comment or thought, I'm just a beginner with WMI.
TIA.
Log Reader:
The process could not execute 'sp_repldone/sp_replcounters' on 'prdSQL01'.
I have configured replication locally; from prdSQL01 to prdSQL01 in order to
troubleshoot this problem.
Is the problem is local to the box?????????
Do I need to re-install MSSQL SERVER? CORRUPT INSTALL????
HELP IS DESPERATELY NEEDED. APPRECIATE ALL SUGGESTIONS.
THANKS
SAM
Unable to start log reader agent after being manually stopped.
Any ideas.
In ReplMon, some Log Reader agent jobs display as "not running". Rigt-clicking on it the only option is to "Stop" the agent. Seems to me if it's NOT RUNNING you should be able to Start it but that option is disabled. Also, looking at the Agent Job Activity Monitor on the distribution server it says that the job is currently executing.
Running the Replication Agents Checkup job yields nothing and those jobs are still "not running". I can easily Stop/Start the Log Reader in ReplMon or insert a tracer token and then all looks fine. I'm just puzzled by the inconsistency and wondering how I can programmatically check and resolve it.
Question:
I am unclear what syntat to use to stop (suspend) the log reader agent.
Things I Understand:
I understand how to manually stop the reader agent using the enterprise manager.
I understand how to schedule the load reader frequency (properties of the agent)
My Intention:
to stop the log reader agent, execute another scheduled job, then restart the log reader
Any suggestions are appreciated: comment here or send to:fglomb@crtincbc.com
Thanks!
As you see in the images the connection is closing. During the read it counts 5 columns which is correct. When I step through the code it closes the connection when it hits dt.Load(reader) and nothing is loaded into the datatable.
------------------------------------------------------------AS I STEP THROUGH -----------------------------------------------------------------------------------------------------------------------
Please help,
Thanks,
Tom
I'm getting this, after upgrading from 2000 to 2005.Replication-Replication Distribution Subsystem: agent (null) failed.The subscription to publication '(null)' has expired or does notexist.The only suggestions I've seen are to dump all subscriptions. Sincewe have several dozen publications to several servers, is there adecent way to script it all out, if that's the only suggestion?Thanks in advance.
View 3 Replies View RelatedHello,
This is info that I am still not certain about and I just need to make sure, my gut feeling is correct:
A.
When a procedure is triggered upon reception of a message in a queue, what happens when the procedure fails and rolls back?
1. Message is left on the Queue.
2. is the worker procedure triggered again for the same message by the queue?
3. I am hoping the Queue keeps on triggering workers until it is empty.
My scenario is that my queue reader procedure only reads one message at a time, thus I do not loop to receive many messages.
B.
For my scenario messages are independent and ordering does not matter.
Thus I want to ensure my Queue reader procedures execute simultaneously. Is reading the Top message in one reader somehow blocking the queue for any other reader procedures? I.e. if I have BEGIN TRANSACTION when reading messages of the Queue, is that effectively going prevent many reader procedures working simultaneously. Again, I want to ensure that Service broker is effectively spawning procedures that work simultaneously.
Thank you very much for the time,
Lubomir
we had a failure on our Publishing SQL Server (2000)
now the distribution agents are marked inactive and must be re-initialized. How do I do this?
Now, I am getting closer and receiving the following message in the Distribution Task (repl_subcriber is the owner, AutoStart is the Frequency):
"37000[Microsoft][ODBC SQL Server Driver][SQL Server] Login failed - User: Reason: Not Associated with a trusted SQL Server connection."
I have 'sqlexecutivent' and 'pswd' defined on the CA (Publisher) and VA (Subscriber) Servers. I have 'sqlexecutivent' defined in the NT Administrator group on both machines. I have trusted the NT Administrator group to SQL Server on both machines (xp_grantlogin 'administrator', 'admin'). I have trusted the NT 'sqlexecutivent' login to SQL Server on both machines (xp_grantlogin 'sqlexecutivent', 'admin'). I have logged-in to both NT machines using the 'sqlexecutivent' login and 'pswd'. I have started the MSSQLServer Service and SQLExecutive Service using 'sqlexecutivent' and 'pswd'. When I set up the Topology, Publication, and Subscription I was registerred in SEM as 'sa'. I do not know if this is needed, but in SQL Server I also created 'sqlexecutive' logins and 'sqlexecutive' database users on both machines.
Please tell me what I am missing. Do I need to do anything with the repl_subscriber and repl_publisher login ids? Do I need to configure a DSN? Do I need to check anything in the registry? Maybe I need to just start over again with a fresh start by uninstalling the Replication Topology, Publishing, etc. Maybe I should try Multi-Protocol.
Again, these are two NT 4.0, SP3 machines running SQL Server 6.5 SP5a. They are set up in the same Workgroup. I can connect to both machines in SEM and by Mapping a Network Drive. The default netlibrary is set to named pipes.
As part of my appreciation for your efforts, I will document this process and mail it to you once it is working.
Thanks, Kevin
I have been attempting to set up replication between two 6.5 servers in the same domain, and all I get is a login failed error on my replication task. Both servers use standard security and an admin login for the server and exec service, and I am using the default remote server setup as installed with replication. I'm sure it will be something simple, but I can't see it.
Thanks in advance for any help you can offer.
Hi Guys im newbie but need to learn more of it so please cooperate with me and slolve my problem.
my Sql Server starts merging and it starts dumping the scema and data after some time it the status shows failed, it doesn't give any error message also. please let me know what to check and how to resolve this.
Hi everybody,
View 1 Replies View Related
Hi..
I've 3 pc that have MS SQL 2000 database installed in it and connected to main server through merge replication.
At first it run fine all the time. But now, it doesn't. It's happen when the pc have to restart every night. When i come to the office and check resplication status it return failed. I try to manually start synchronize but it failed too. I've to restart the pc all over again untill it ok.
I hope some body out there can explain something to me and how to overcome the problem...
Hi All,
We have a SQL server 2000 merge replication and it has been running for couple of years. We were even fine too at the beginning after upgraded our publisher SQL Server 2000 to SQL Server 2005. Now we are starting have problems. I just found there were two files seem to be too big, MSmerge_genhistory.bcp (1,830,287K) and MSmerge_genhistory90.bcp (1,977,746K). I have no idea why these two files are so big.
My questions are:
1. Why these two files? (we do have a mix of SQL Server 2000 MSDE and SQL Server 2005 Express as the subscribers)
2. Can (Should) I clean the MSmerge_genhistory table? How?(sp_mergemetadataretentioncleanup)
3. What could be the impact to all the existing subscribers if I clean this table?
Thank you for your help.
Yong
Hi all,
I hope someone who knows how to fix the following problem could shed some light:
I have set up a two-way transactional replication between two SQL 7.0 servers. The updates and inserts on server A is successfully replicated to server B. But when I tried to test if changes on server B is replicated onto server A, I got a 'login failed for user 'sa'' when I modified a user table. It sounds strange because I already logged in using 'sa' and all SQL servers use the same 'sa' login. I wonder if it is a Microsoft bug. The same problem happened on a pair of SQL 2000 Enterprise servers.
Thanks for any input.
Richard