December 24, 2007 at 5:27 pm
Guys,
I have a transactional replication set up as such: Publisher - Distributor - 2 subscribors. Everything has been working fine for a long time. Last night replication failed. I checked and saw that we don't have replicated data since 23:55PM of yesterday.
The Log Reader Agent is the one having the problem, with the following error message "The process could not execute 'sp_MSadd_repl_commands27hp' on 'SQLCLUSTER01\DIST01'" and the following error details:
The SQL Server cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users or ask the system administrator to check the SQL Server lock and memory configuration.
(Source: SQLCLUSTER01\DIST01 (Data source); Error number: 1007)
---------------------------------------------------------------------------------------------------------------
IDistPut Interface has been shut down.
(Source: (Agent); Error number: 2000)
---------------------------------------------------------------------------------------------------------------
I restarted the log agent a few times, and it showed that it is running, although no new data was replicated, and after some time it failed again.
I assumed that it is possible that there are too many transactions to replicate (as it is behind about 19 hours now, and all of it needs to be replicated), and so I increased the "Query Timeout" in the agent profile to 100,000, but that did not help either. It failed again in an hour or so.
In the meantime, the transaction log in the Publisher keeps growing, as it cannot be backed up until the data gets replicated.
Does anyone have any suggestions on how this replication problem can be fixed?
Thanks a lot in advance!
December 25, 2007 at 7:56 am
I got my replication issues fixed by reducing the batchsize from 500 to 100.
December 26, 2007 at 6:00 am
Their is option to reintilize the whole Transaction Replication. After that it will transfer all the log to Subscriber.
December 26, 2007 at 1:50 pm
The problem got solved by clearing the log as stated here: http://www.thescripts.com/forum/thread79839.html
and then deleting and re-adding the subscribers ...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply