0

Background:

I have a SQL Server 2005 setup with master, slave1, slave2 replication set up as a pull replication from slaves. The distribution database resides on the slave1 machine, both slaves pull.

A problem began today where the replication on slave1 simply stops running. It claims that it completed successfully, but it does not restart, and manually starting the process finishes in roughly one minute, again without an error message.

Screenshot

Replication is running fine on slave2, but I can't seem to figure out what's wrong on slave1. I've tried the obvious Windows debugging 101: "restart the machine" technique, but to no avail.

Has anyone encountered this before Does anyone have an idea of what I could check or change to get it working again? I'm especially at a loss as SQL Server claims that the job is just finishing successfully.

Improve this question
2
  • Have you checked the job schedule?
    – Hannah Vernon ♦
    Nov 2, 2013 at 14:03
  • @MaxVernon, the job scheduler just says "Start when SQL Server starts", on the other machine, it's the same. Previously it had just continued running without stopping, and now it stops for unknown and unstated reasons.
    – Tom Jacques
    Nov 2, 2013 at 19:24

2 Answers 2

Reset to default
2

SQL Server Replication is a notorious error hider.

Usually if replication just stops, a problem occurred and there is on place where you can see what that problem is. All other places you might look at show no sign of trouble.

Places to include when hunting for errors:

  1. The replication monitor
  2. The job history of each job involved (Look at the history. Even the SQL Agent Monitor hides a replication problem every once in a while.)
  3. The SQL Server error log
  4. The windows event log

3 and 4 are rarely needed. 2 is usually all you need to look at to find the problem if 1 did not help.

There is a lot more information about potential errors and how to identify them in my replication stairway articles. Particularly in Level 10.

Improve this answer
1
  • Hi Sebastian. I did check all of those locations, with no luck. The replication monitor just says "Not running performance critical", with a success/no error on the previous run. The job history lists some additional information, such as how many commands were successfully executed, etc. but does not show any error. The SQL Server log and windows event log also do not have any entries. I'm pretty stumped at this point, thinking I may just have to wipe it and start again.
    – Tom Jacques
    Nov 2, 2013 at 19:15
0

I'm still not sure why this is happening, but I narrowed it down to the replication profile settings that I was using. I have a higher performance replication profile, and when it is selected, the replication job simply finishes after running for a short while. It did not do this in the past, but using the default replication agent profile settings seems to have it working.

Improve this answer

Your Answer

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

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