none
Cannot create publication after SP1 upgrade RRS feed

  • Question

  • After upgrading SQL Server 2005 with the recent service pack, I am unable to create a publication.  When I right-click on [INSTANCE]/Replication/Local Publications and select "New Publication...," the mouse cursor turns into an hourglass, but the wizard never shows up, nor is any error displayed.

    There is a logged error that's been showing up that may or may not be related.  The error message is "Replication-(null): agent (null) scheduled for retry.  Could not clean up the distribution history tables."

    Any ideas on how we can restore replication functionality on our SQL Server instance?

    Wednesday, May 24, 2006 10:26 PM

Answers

  • It's finally working!  I suspect that all the problems stemmed from there being an inconsistency in the sysdatabases table.  I ran "DROP DATABASE distributor" under the master database, and afterward, I was able to set up the distributor, publishers, and subscribers without any problems.

    Thank you, Zhiqiang Feng, for your email assistance.

    Saturday, May 27, 2006 1:36 AM

All replies

  • Can you run profiler before invoking New Publication wizard to trace which statement is causing the problem? What kind of publications do you have before upgrade to SP1? Is distributor on the same machine as publisher?

    Thanks,

    Zhiqiang Feng

    This posting is provided "AS IS" with no warranties, and confers no rights.

    Thursday, May 25, 2006 4:41 AM
  • I ran the profiler, and I'm getting some strange output.  Apparently, the command "select * from sysdatabases where name=N'distribution'" is being run over and over again.  Strangely enough, there is a row returned from that command that points to where our distribution database was located, but the database isn't shown under the system databases in Management Studio.

    Before upgrading to SP1, we were using merge publications with the distributor on the same machine as the publisher.

    Thursday, May 25, 2006 4:22 PM
  • Can you send sp1 upgrade log files and profiler trace to me? I will send you a separate email.

    Thanks,

    Zhiqiang Feng

    This posting is provided "AS IS" with no warranties, and confers no rights.

    Thursday, May 25, 2006 4:44 PM
  • From the sp1 upgrade log file, looks like you have run SP1 upgrade twice, the first time it runs, SQLDEV01 machine is treated as a distributor and distribution database is upgrade successfully(from what the log file says). The 2nd time the sp1 upgrade runs, it didn't treat this machine as distributor and didn't upgrade distribution database (also you mentioned distribution database is no longer marked as system database, which is suspicious, we need to figure out why).  Did you notice any error during sp1 upgrade? Why you run sp1 upgrade twice? Did you drop distributor or change replication settings after first sp1 upgrade?

    Please also email a profiler trace for the New publication wizard so we can further diagnose the issue. When using profiler, make sure select following options (and unselect any options not mentioned below):

    Error and Warnings --> ErrorLog, Exceptions, User Error Message

    Stored Procedures --> RPC Starting, RPC Completed, SP Starting, SP Completed, SP Stmt Starting

    TSQL --> SQL: Batch Starting, SQL: Batch Completed, SQL: StmtStarting

    Thanks,

     

    Zhiqiang Feng

    This posting is provided "AS IS" with no warranties, and confers no rights.

    Thursday, May 25, 2006 6:40 PM
  • It's finally working!  I suspect that all the problems stemmed from there being an inconsistency in the sysdatabases table.  I ran "DROP DATABASE distributor" under the master database, and afterward, I was able to set up the distributor, publishers, and subscribers without any problems.

    Thank you, Zhiqiang Feng, for your email assistance.

    Saturday, May 27, 2006 1:36 AM