none
sp_replmonitorhelpsubscription returning wrong status?

    Question

  • Hi All,

    I am looking at using the output of sp_replmonitorhelpsubscription as a basis for a monitoring script. I was hoping that the [status] column was accurate but now i'm not so sure.

    BOL defines it as "Examines the status of all the replication agents associated with the publication, and returns the highest status found in the following order :

    6 = Failed

    5 = Retrying

    2 = Stopped

    4 = Idle

    3 = In progress

    1 = Started"

    When I run it against one of my publications that, under Replication Monitor --> Agents -- shows Queue reader agent as Failed, but 'Log Reader Agent' as Running...it returns a status of 3 (IN PROGRESS).

    I would have thought that based on the Queue Reader Agent status which is FAILED, the status should return a value of 6 not 3.

    The problem now is that since this transactional replication is constantly trying and failing (due to a PK constraint which we are fixing), the only time this SP outputs a value of 6 is in between retries.

    In my mind, it should return a value of 6 even though it is retrying.

    Am I missing something here?

    Thanks in advance.

    Wednesday, April 02, 2014 8:42 PM

All replies