March 29, 2003 at 9:35 pm
I'm running transaction replication on SQL2000 advance and the log reader cannot start
Error message from log reader
The process could not execute 'sp_replcmds'
Timeout expired
(Source: ODBC SQL Server Driver (ODBC); Error number: 0).
March 30, 2003 at 1:58 pm
Try increasing the timeout on the logreader. I've seen this happen when there is an extremely large transaction in the log, the log reader has to read through all of it to get done, times out before it does. An index rebuild is one way this can happen, mainly if the log reader is not running in continuous mode.
Andy
March 30, 2003 at 7:08 pm
Hi Andy,
Thank for your reply. After I increasing the timeout for log reader. I encouter another problem on the publisher.
The process is running and is waiting for a response from one of the backend connections.
March 30, 2003 at 7:12 pm
On the distribution agent you mean?
Andy
March 30, 2003 at 7:12 pm
Yes.
April 1, 2003 at 5:46 am
Also have the same problem, and is because of a large transaction.
If you have the MaxDeliveredTransactions equal to 0 (wich means all transactions) increase the value to for example 10 and re run the distribution agent. If the problem conitnues, decrease a bit more the value (for example to 5) and if it continues, decrease to 1 wich means that the next transaction to be delivered to the distribution db, is the one that is causing the problem.
April 1, 2003 at 5:55 am
Hi Racosta,
May I know how and where to increase or descrease the MaxDeliveredTransactions?
Thanks in advance.
April 1, 2003 at 6:32 am
Sorry, my mistake.
With EM, go up to replication monitor and find the subscription with the error. Right click over it and click in Agent Profile.
Check wich one is SQL using. If it is a System Profile, you won't be able to change. So create a new one (wich is is created
as the deafult) and check the MaxDeliveredTransactions value.
Remeber to check the profile you created to order SQL to use the new profile. Then run the distribution agent again and do as I told you
before.
If you are running the Distribution agent with the command distrib.exe (wich I use to determine errors, because it shows more info
and also I recommended it), you must change the profile with EM, and then in the parameters of the distrib.exe,
add the name of the profile in -ProfileName option.
Hope it works.
April 1, 2003 at 5:37 pm
Thanks. I will let you know the status later
April 3, 2003 at 6:06 pm
Hi All,
Finally, the problem managed to solved by using
EXEC sp_configure 'locks', '500000'
RECONFIGURE WITH OVERRIDE
Thanks for all the helping hand 🙂
April 4, 2003 at 6:45 am
Wow - I've never had to change that. Working is better than not, but I'm not sure you've gotten to the bottom of your issue.
Andy
April 4, 2003 at 7:16 am
Yes, unless SQL Server displays a message that you have exceeded the number of available locks, I wouldn't change it. Maybe SQL manages to replicate to the distribution the conflicting transaction.
Also the message:
'The process is running and is waiting for a response from one of the backend connections' also means a locking problem on the log reader.
How did you set the value for MaxDeliveredTransactions ?
April 6, 2003 at 12:52 am
Actually, My senior DBA is helping me on this issue as well. He said by using QA and run the sp_configure. For more detail, can find it in book online.
April 7, 2003 at 12:31 am
Apparently corrupted LSN information is involved in this problem. Resetting its value in the log reader may help, if more simple causes are discarded (cpu overloaded by other services, slow connections, large transactions).
Now, manually resolving this problem involves many steps and could mean some loss of synchronicity and manipulation of the distribution database tables.
Obviously, the classic solution: uninstall, reinstall would work, but I hope this is a feasible and last option.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply