Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sql_er
Constraint Violating Yak Guru
267 Posts |
 Posted - 2007-12-24 : 19:19:09
|
| 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! |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-12-24 : 21:39:31
|
| Anyone holds lock on subscriber? Does subscriber allocate locks dynamically? |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-12-24 : 23:48:24
|
| How much memory do you have on your server? How much is SQL Server allowed to have?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
sql_er
Constraint Violating Yak Guru
267 Posts |
Posted - 2007-12-26 : 15:49:21
|
| rmiao: I am not sure if anyone was holding locks on subscribertziker: 15GB each I believe ...I think the problem was due to an open transaction.We solved the problem as such:1. Marked all transactions in publisher as distributed2. cleared out the trans log at publisher3. made a full backup of publisher db4. deleted and re-added both subscribersWe basically followed the advice here: http://www.thescripts.com/forum/thread79839.htmlNow it is all working ...BTW, is there a way of closing an open transaction, which has been open for a long time ???Thank you |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-12-26 : 15:54:15
|
quote:
Originally posted by sql_er
BTW, is there a way of closing an open transaction, which has been open for a long time ???
KILL spidNoTara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
sql_er
Constraint Violating Yak Guru
267 Posts |
Posted - 2007-12-27 : 12:28:55
|
| Thanks |
|
|
|
|
|
|
|
|
Log reader agent fails continuously