4

We are running transaction replication on SQL Server 2008 R2 SP3. Distribution agents are reporting :

"Agent is retrying after an error. 3 retries attempted"

every 10 minutes. Tried changing the query timeout values, restarted SQL Server agent and stopped and restarted the distribution agent. There are no errors in the job log, SQL Server error logs or windows event logs that appear relevant.

Improve this question
5
  • For more detailed information, make sure to set your -OutputVerboseLevel 2 as recommended at http://support.microsoft.com/kb/312292/en-us. This will provide you more detailed information on the problem
    – RLF
    Commented Jan 6, 2015 at 16:23
  • We did that and are reviewing the output. Thanks
    – PMSawyer
    Commented Jan 7, 2015 at 13:36
  • 2
    The problem has been solved. It was caused by some bad job duration values, negative, in the job history table. Deleting the bad data from the table solved the problem. Speculation how it happened points to the server clock being incorrect then corrected causing the bad values.
    – PMSawyer
    Commented Feb 17, 2015 at 21:16
  • @PMSawyer, it would be extremely useful if you could add your comment as an answer (with more details if possible). I do of course realize it's been more than a year since this question was active.
    – Santa
    Commented Nov 19, 2016 at 10:21
  • @PMSawyer thanks for the note about server clock being incorrect. I'm investigating negative job durations and after reading your comment found the server clock appears wonky with sequential EventRecordID values following out of order time entries in the event viewer.
    – mlhDev
    Commented Jan 14, 2019 at 13:36

1 Answer 1

Reset to default
1

Note: This answer is based on OP's comment under the question. So credit goes to them for finding the solution.

Faced this issue my self, which is how I ended up here. Like @PMSawyer said, there was a negative value in the run_duration column of sys.jobhistory for the Agent job.

The following query can be used to find if there are any negative values.

SELECT * FROM msdb.dbo.sysjobhistory WHERE job_id = ( SELECT job_id FROM msdb.dbo.sysjobs WHERE name = 'INPUT_YOUR_AGENT_JOB_NAME_HERE' ) AND run_duration < 0 ORDER BY run_date DESC

If this query produces an output, it is very likely that that is what is causing the problem. The negative value will need to be removed by purging the agent job's history, the following query can be used for that.

msdb.dbo.sp_purge_jobhistory @job_name = 'INPUT_YOUR_AGENT_JOB_NAME_HERE'

The output would look something like: 12 history entries purged..

Purging the job history doesn't affect replication in any way, in case that's a concern.

Also, in my case the reason this negative value came to be was that the server time had changed in accordance with Day Light Savings. The server clock and moved an hour behind.

On an unrelated note, I don't know fancy techniques like linking to OP's comment that provided the basis for this answer, if anyone cares enough to do that, it'd be great.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.