SQL Server Performance

Replication Problem

Discussion in 'Performance Tuning for SQL Server Replication' started by vn, Apr 15, 2004.

  1. vn New Member

    I am not able to disable or enable publishing on our database server. We recently had to restore our entire database server. And, when I restored the database server, my distribution database was marked suspect. Since, I felt I could re-enable replication I detached and dropped the distribution database. When I try to disable publishing I get an error saying "could not locate distribution database in sysdatabases". When I try to enable publishing I get an SQL-DMO error 21769 (The passed ordinal is out of range of the specified collection). Now I am not sure how to set this right. Please help. Thanks.
  2. satya Moderator

    Basically appears that the distribution database has been corrupted, try to perfom the following:

    1. From Query Analyzer, run this SELECT statement: SELECT * from msdb..msdistributiondbs
    2. If a row is returned delete it.
    3. Then, reconfigure replication. From Enterprise Manager, go to Tools->Replication-> Configure publishers, subscribers,and distribution.
    4. Run Step 3 again, and you should then be able to create the distribution

    If that doesn't work, you may need to remove replication from the server and reestablish replication.

    Please be aware that these steps will remove replication from the server. If you are uncertain about how to re-establish replication, refer to books online for more information.

    1. From Query Analyzer, run the following commands:
    sp_configure 'allow',1 go
    reconfigure with override go
    DELETE master..sysservers WHERE srvname = 'repl_distributor'

    2. Re-run the replication wizard and re-establish replication on the server.


    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.
  3. vn New Member

    Thanks for replying! I was successfully able to create the publication that I wanted. But, now I have a new error when I try to create a push subscription. I get an error message saying "The server '%s' is already a Subscriber." (error 14040). How do I correct this error? Thanks again for helping.
  4. satya Moderator

    Run select * from master.dbo.sysservers
    --- onpublisher, distributor and subscriber and post the results.

    #And also ensure SELECT @@SERVERNAME doesn't return NULL,if so then drop the servername and add using SP_DROPSERVER & SP_ADDSERVER statements.
    Refer to books online for more information.

    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.
  5. vn New Member

    I think I kind-of solved it! I executed sp_dropsubscriber on the publication database and I was then able to create the replication. This morning I also checked that the replication had worked fine at the time that I had scheduled for it. But, the distribution agent seems to have run one more time and I dont know why. I checked the schedule for all the agents and none of them were supposed to have run at that time. I dont know why it ran again at 9.30pm when I had scheduled only for 7:30 PM. The 7:30 distribution seems to have worked fine. But for the 9:30 run, for one publication I get an "information" message saying "No replication transactions are available", on the other publication I get a message saying "The agent is suspect. No response in the last 10 minutes". I am wondering if there are still more things that I need to set right to bring my database server to where it was before it crashed. How do I troubleshoot this? Thank you.
  6. satya Moderator

  7. vn New Member

    Thanks again for the help. I tried to completely disable publishing on the server. I deleted all the subscriptions, then the publications and then I disabled Publishing and Distribution. But, I am still finding a jobs listed in the SQL Server Agent of type replication (REPL-Distribution and REPL-Snapshot). And, the time of last run is around the time that I am seeing the error messages on the job history. I am thinking that the restore of the msdb database created those jobs and I added to the list of jobs by creating the replication again. Now when I disable publishing and distribution, the jobs are not getting deleted (the first set that resulted from restore of msdb). I dont know if it is safe to delete the jobs.
    The reason why the distribution was suspect in the first place - I restored the master database which put the distribution database in the suspect mode (the server was set for replication when our system crashed). But, I did not have a backup of the distribution databases. So, I could not restore the distribution database.
    Is it okay to delete the jobs without corrupting anything again?
    I will setup the agents for logging and keep you posted with what I find. Thank you for the help.
  8. satya Moderator

    If you're going to re-build replication from scratch, its better to delete all replication related jobs from MSDB database.

    As MSDB is restored, its better to disable them atleast on the first point and create a fresh ones to take care of replication schedule.

    If you're restoring the MASTER database, ensure to disable the replication. Otherwise these kind of issues will overcome which is an additional hassle to clear. Once the system is setup to the level of online then you can restore the distribution and other replication databases to the normal level.

    Refer to books online about restoring the replication databases.

    HTH

    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.
  9. vn New Member

    I have been successfully running replication the last one week. But, I did not have to do the steps that you mentioned. But, how do I find out the list of all system tables that store replication related information? I saw a few tables that had "distribution" or "distributor" word in them, but I do not have a complete list. Thanks.
  10. satya Moderator

    The best bet would be to refer books onine for the topic System tables - replication information.


    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