none
SQL Replication - distribution database RRS feed

  • Question

  • I can't use the distribution database because it doesn't exist, and I can't create the distribution database because it already exists.

    A failed replication set up had left a fat distribution database that caused the server CPU to peg 100%
    when I tried to set up a new Transactional replication (halfway through the wizard).

    I removed all replication objects from SMSS, but the only thing that fixed it was deleting the distribution
    database with an ALTER to OFFLINE and a DROP, and restarting SQL.

    Now, I'm unable to set up replication.

    When I try, I get the error "Database 'distribution' does not exist. Make sure that the name is entered correctly."

    In Distributor Properties, the Publisher is shown with a distribution database of 'distribution'. If I
    untick that, SQL asks if I want to disable the Publisher. I say yes & get the error "Database
    'distribution' does not exist.'

    Ok, so let's create it.  In the General tab of that window I create a new Distribution database and call it
    'distribution'.  Then I get the error 'Could not add the distribution database 'distribution'. This
    distribution database already exists.


    exec sp_helpdistributor    shows distribution database as 'distribution'
    SMSS doesn't show the distribution database at all.

    Any ideas on how I can proceed?

    Thursday, April 5, 2012 5:09 AM

Answers

  • If the distribution database was set offline first then dropped, the files should still exist.  This can be simulated with a test database easily.

    For example, if I create a database TestDB1, then set it offline and drop it:

    use master
    go
    alter database TestDB1 set offline;
    drop database TestDB1;

    The disk files will still exist.

    By the way, that article is a horrible recommendation.  The distribution database should never be dropped in that fashion.  I have a post discussing that here: http://www.sqlrepl.com/sql-server/accidentally-dropped-distribution-database/.


    Brandon Williams (blog | linkedin)

    • Marked as answer by Garetht2 Monday, April 9, 2012 5:41 PM
    Thursday, April 5, 2012 5:31 PM
    Moderator

All replies

  • Run this command 

    sp_dropdistributiondb 

    you can get more information about this command here  Once this command is completed run the below command. 

    sp_dropdistributor

    you can get more information about this command here   

    These should help you to remove distributor and then recreate them. If you get any error while running the last command then use the parameter

    ignore_distributor


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    Thursday, April 5, 2012 7:28 AM
  • Hi

    I've sanitised the names a little : )

    sp_dropdistributiondb @database = 'mydatabase'

    Msg 14117, Level 16, State 1, Procedure sp_dropdistributiondb, Line 73
    'mydatabase' is not configured as a distribution database.


    sp_dropdistributor

    Msg 14121, Level 16, State 1, Procedure sp_dropdistributor, Line 211
    Could not drop the Distributor 'MYSERVER'. This Distributor has associated distribution databases.


    sp_dropdistributor @ignore_distributor = 1

    Msg 14121, Level 16, State 1, Procedure sp_dropdistributor, Line 211
    Could not drop the Distributor 'MYSERVER'. This Distributor has associated distribution databases.

    Thursday, April 5, 2012 4:58 PM
  • Garetht2,

    The distribution database should never be dropped by setting it offline and dropping it.

    However, considering you set the distribution database offline and dropped it, the disk files have not been deleted.  Locate the distribution.mdf and distribution.ldf files and re-attach them.  So if the distribution database files are located in C:\DATA\ then they can be re-attached like this:

    USE [master]
    GO
    
    CREATE DATABASE [distribution] ON
     ( FILENAME = N'C:\DATA\distribution.mdf'),
       FILENAME = N'C:\DATA\distribution.ldf')
    FOR ATTACH
    GO

    Then use the Disable Publishing and Distribution wizard to disable publishing and distribution and start over.


    Brandon Williams (blog | linkedin)

    Thursday, April 5, 2012 5:14 PM
    Moderator
  • Unfortunately, the disk files have indeed been deleted.

    Info on dropping the distribution database came from http://weblogs.sqlteam.com/tarad/archive/2009/09/02/How-to-remove-a-SQL-Server-distribution-database-if-other.aspx

    Thursday, April 5, 2012 5:25 PM
  • If the distribution database was set offline first then dropped, the files should still exist.  This can be simulated with a test database easily.

    For example, if I create a database TestDB1, then set it offline and drop it:

    use master
    go
    alter database TestDB1 set offline;
    drop database TestDB1;

    The disk files will still exist.

    By the way, that article is a horrible recommendation.  The distribution database should never be dropped in that fashion.  I have a post discussing that here: http://www.sqlrepl.com/sql-server/accidentally-dropped-distribution-database/.


    Brandon Williams (blog | linkedin)

    • Marked as answer by Garetht2 Monday, April 9, 2012 5:41 PM
    Thursday, April 5, 2012 5:31 PM
    Moderator
  • As part of trying to get the distribution database back, as it wouldn't create the distribution database saying it already exists, I'd deleted the distribution db's files from disk.
    Thursday, April 5, 2012 5:47 PM
  • This is a tough spot then.

    I'm unable to setup a repro for this until 5pm Pacific time.  If you can wait that long - I can see if there is a way to recover from this.


    Brandon Williams (blog | linkedin)

    Thursday, April 5, 2012 5:53 PM
    Moderator
  • ah!

    Thank you very much for your questions.  They made me go back, check, slap my forehead, and I have a backup of the distribution database.

    I've now restored this.

    This puts me back where I started last night -

    The distribution database has 5.3 GB of data from my first failed replication attempt.

    When I start to set up a new replication, using the wizard, once I have selected all the articles the CPU pegged 100% until I killed the distribution database.  How can I clean this out so it doesn't think it has 5GB of data to work through?

    Thursday, April 5, 2012 6:26 PM
  • Hmm..  I've opened a support case with MS for this.  I'll update with any info.
    Thursday, April 5, 2012 10:45 PM
  • SQL contained a plethora of metadata about the failed replication.
    After a couple of hours Support was able to clear these & get me back to a stage of zen emptiness.

    The commands I was able to save:

    Show or delete distributors.
    select * from msdb.dbo.MSdistpublishers
    delete from msdb.dbo.MSdistpublishers

    Drop distribution database
    sp_dropdistributiondb 'distribution'

    Drop Distribution
    USE master
    GO
    EXEC sp_dropdistributor @no_checks = 1

    Show publication database
    sp_helptext 'sp_MSpublishdb'
    Monday, April 9, 2012 5:40 PM
  • I have the same problem.

    Thanks

    Friday, May 25, 2012 5:55 AM
  • THE LINK YOU PROVIDED SIR, IS NOT ACCESSIBLE.
    Friday, May 29, 2020 10:34 PM