Post reply

Cannot create replication because old replication is not cleaned up

  • October 3, 2012 at 2:28 pm

    #268746

    We have a server that used to be a publisher and distributor for a couple of simple transactional replications. At some point in time all replications have been removed from here.

    Now I am tasked to re-create these replications. In a Publication wizard, at the very last step, I get an error message "Database 'distribution' does not exist. Make sure that the name is entered correctly."

    OK, in Replication folder/Distributor properties, the General section is empty, but the Publisher section contains one record with the Publisher as 'Server_Name' and the Distribution Database field has an entry 'distribution'.

    Among system databases, there is no Distribution database.

    If I run exec sp_helpdistributor, it has one record which is the same as the one in Distributor Property GUI described above.

    exec sp_helpdistributiondb has no records. But for sp_helpdistributor_properties I do not have privileges.

    I also tried to run exec exec sp_dropdistributor, but got a message "Could not drop the Distributor 'Server_Name'. This Distributor has associated distribution databases.

    How can I clean all these up ? As a workaround, can I create a replication without distributor ? Or a distributor on a subscriber side ? Any help is appreciated.

    Thanks

  • October 3, 2012 at 2:36 pm

    #1545215

    Have you tried adding a distribution database and then running sp_dropdistributor?

    http://msdn.microsoft.com/en-us/library/ms189755.aspx

  • October 3, 2012 at 2:39 pm

    #1545216

    SQL Guy 1 (10/3/2012)

    As a workaround, can I create a replication without distributor ?

    No

    SQL Guy 1 (10/3/2012)

    Or a distributor on a subscriber side ?

    Any sql server can be a distributor (providing replication is installed) but I think youre publisher will still have a problem as it can only have one distributor which is causing this problem.

  • October 3, 2012 at 2:42 pm

    #1545219

    If you had any published databases, try changing the the db option before running sp_dropdistributor as this is the standard process I follow.

    USE [Database]; EXEC sp_removedbreplication 'Database'

    GO

    USE [Database1]; EXEC sp_removedbreplication 'Database1'

    GO

    EXEC sp_dropdistributor;

    GO

  • October 3, 2012 at 2:57 pm

    #1545229

    I ran both

    USE [Database]; EXEC sp_removedbreplication 'Database'

    GO

    USE [Database1]; EXEC sp_removedbreplication 'Database1'

    GO

    But when I run EXEC sp_dropdistributor, I get the following error:

    Msg 14121, Level 16, State 1, Procedure sp_dropdistributor, Line 211

    Could not drop the Distributor 'Server_name\Instance_name'. This Distributor has associated distribution databases.

    Apparently, there are also other databases , besides my 'Database' and 'Database1' associated with replication. How can I find out which ones ?

  • October 3, 2012 at 3:07 pm

    #1545235

    Meanwhile, I created dynamic script to run EXEC sp_removedbreplication from all databasses on my server and ran it.

    However,

    EXEC sp_dropdistributor;

    still generates error:

    Msg 14121, Level 16, State 1, Procedure sp_dropdistributor, Line 211

    Could not drop the Distributor 'My_Server_Name'. This Distributor has associated distribution databases.

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

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