SQL Server Performance

HELP: REPLICATION CRASH

Discussion in 'Performance Tuning for SQL Server Replication' started by raulmoi, Apr 8, 2004.

  1. raulmoi New Member

    I desperately need help and amd new to SQL SERVER. I had the following disaster happen to me.

    1. There are two database servers with replication turned on.
    2. SQL-B has a database called DB is pushing data to SQL-A.
    3. On SQL-A, there are SQL Agent Jobs that are running REPLICATION DISTRIBUTOR
    4. SQL-B crashes severly to the point where DB is lost.
    5. A successful restore of DB was accomplished by getting an earlier backup.
    6. A few days passed and it was noted that the jobs mentioned in step #3 have dissappeared. It is not in MSDB and sad to say, MSDB was nevered backup.


    Questions:
    a. Could the a crash on one server cause the disapperance of jobs on the a second server? Since there were 4 jobs that belong to the same replicated database, its hard to rule this out like someone accidently deleted them.

    b. Is looks like to me that the jobs mentioned in step #3 were created by SQL Server through some kind of wizard. Is this true?


    PLEASE RESPOND ASAP!!!


  2. NewDBA Member

    It most probably that SQL Server deleted you subsription and together with that
    the job that distribute data.

    There is job on server called
    "Expired subscription clean up"

    If your server shutdown and replication failed for certain amount of time
    then your subscription will be expired.

    I disabled that job on my server
  3. NewDBA Member



    You have to recreate the sucscription

  4. raulmoi New Member

    What is the specific "sp" command that will do that? From what I can see of the jobs that fit your description, they are,

    sp_replication_agent_checkup
    sp_MSreinit_failed_subscriptions
    sp_MSdistribution_cleanup
    sp_MShistory_cleanup

    Thanks
  5. NewDBA Member

    First at all it's a job I refered above.<br /><br />To recreate subscription: <br />1.On you publisher server go to Tools-&gt;Replication-&gt<img src='/community/emoticons/emotion-4.gif' alt=';P' />ush Subscription to Others on your <br />tool bar.<br />2.Highlight your publication.<br />3.Click Push new Subscription <br />4. Follow the wizard
  6. satya Moderator

    Questions:
    a. Could the a crash on one server cause the disapperance of jobs on the a second server? Since there were 4 jobs that belong to the same replicated database, its hard to rule this out like someone accidently deleted them.

    Yes if the other server service is crashed completed then ensure MSDB is working, if not you need to restore to get those jobs back to schedule.

    b. Is looks like to me that the jobs mentioned in step #3 were created by SQL Server through some kind of wizard. Is this true?

    If you haven't used any scripts to create replication then yes, EM will create when you use Replication wizard.



    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page