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.

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 Jan 6 '15 at 16:23
  • We did that and are reviewing the output. Thanks – PMSawyer Jan 7 '15 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 Feb 17 '15 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 Nov 19 '16 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 Jan 14 '19 at 13:36
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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