Post reply

Update Replication Agent stale check job

  • July 19, 2011 at 10:50 pm

    #241105

    I have to update Replication Agent Stale check job, provided script for the job that included 4 temporary tables. Altogether we have 5-7 servers (DEVDB2 Devlopment, PRODDB2,PRODDB3\R1, DMZSQL1,DMZSQL3 and DMZSQLTEST production server).

    This job is on PRODDB3\R1 (and the others) just needs to ignore the distribution agents on there that are stalled but going to DEVDB2 (During the Sunday and Monday morning).

    As far i know i might have to make some changes in sp_MSenum_distributionprocedure.

    Experts can you please help with that?

    Thanks in advance and please let me know for any information required.

  • July 19, 2011 at 11:02 pm

    #1355341

    Hi

    What are you trying to acheive ?

    Cheers

    Jannie

  • July 19, 2011 at 11:06 pm

    #1355342

    Basically we are trying to have those agents not running during the Sunday (On DEVDB2) and we just want to make sure that we don't alert on (PRODDB3/R1)these during that time.

    Distribution agent : PRODDB3/R1

    Subscriber : DEVDB2

  • July 19, 2011 at 11:12 pm

    #1355347

    ok

    well there are only 2 agents of major concern

    1 - log reader, if this don't run log run out of space etc or people get grumpy cause they don't get their data.

    2 - distribution , if this don't run your subscription expire or the above.

    So

    You are heading in the right track with those procs but there is a simpler way.

    In the distribution database you can get the job id.

    from mslogreader_agent and msdistribution_agent. (soemthing like that)

    The job_id then ties up to the job that needs to run.

    This proc will enum the current job status, use this instead of sysjobhistory as it might get truncated etc.

    EXEC master.dbo.xp_sqlagent_enum_jobs @is_sysadmin = 1, @job_owner = '';

    Join the two and see if your job is running.

    if you google for this xp_sqlagent_enum_jobs it will give you the status lookup but I think running is a 1.

    Cheers

    Jannie

  • July 19, 2011 at 11:25 pm

    #1355353

    Is there any way you can script that out for better understanding?

    I think i am not getting it correctly.

    Thanks for your prompt reply.

  • July 20, 2011 at 12:27 am

    #1355375

    Well you need need to find out if you replication agent is delivering (running) on a sunday?

    Replication use jobs to kickoff the exes that run replication. So technically you need to find out if the job is running on sunday.

    Now the question , which job.

    The replication agent job of course, you can get the job_id from the distribution database in the table mslogreader_agent, msdistribution_agent.

    Now that you have the job to check you can use the xp_enum job proc to output the list of running jobs.

    Once you have the list of running jobs, youjust search for your job_id

    Once you pair the two, you look at the status.

    If the status is 1 on sunday. It means the replication is running

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply