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 |
|
gogetter
Starting Member
18 Posts |
 Posted - 2008-03-28 : 12:11:26
|
I have transactional replication running in a production environment with the following configuration: Publisher - SQL 7.00.961Subscriber - SQL 2000 SP4 named instanceDistributer - SQL 7.000.961Up until two days ago everything was working perfectly, but all of a sudden the log reader on the distribution server began timing out and producing this message: TimeOut Expired: The process could not set the last distributed transaction.Now I have tried creating a new log reader profile and increasing the login and query timeout properties to very high values but the timeout messages appear to pop up at the same intervals as they were under the default log reader profile. From the research I have done it appears that the timeout is occuring due to the large number of transactions that need to be read by the log reader and applied to the subscriber; so I need to know if there is anyway to set the log reader query timeout to an infinite value. I tried setting it to zero and I got an error message stating that zero was not a positive integer value. Can someone tell me if this is possible in my current configuration? I would love to upgrade everything to SQL 2005 but due to limitations with our legacy applications this is not a feasible solution at this time. Thanks..... |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-03-28 : 14:09:43
|
| Set the Log Reader Agent profile QueryTimeout value to 0 or higher value and decrease the ReadBatchSize, which controls the maximum transactions read from the transaction log of the publisher at a time, thus decreasing the time it takes for the Log Reader Agent to process a batch to the distribution database. |
 |
|
|
gogetter
Starting Member
18 Posts |
Posted - 2008-03-28 : 15:34:38
|
quote:
Originally posted by sodeep
Set the Log Reader Agent profile QueryTimeout value to 0 or higher value and decrease the ReadBatchSize, which controls the maximum transactions read from the transaction log of the publisher at a time, thus decreasing the time it takes for the Log Reader Agent to process a batch to the distribution database.
I tried reducing the batch size but it didn't make a difference. I havd already increased the login and query timeout properties to very high numbers but still get the same errors. Any other ideas? |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-03-28 : 22:49:20
|
| Is there any network issue? Tried put distributor on sql2k? |
|
|
|
|
|
|
Replication Agent Log Reader Keeps Timing Out