Results 1 to 15 of 15
  1. #1
    Join Date
    Apr 2012
    Posts
    8

    Unanswered: Quantum distribution database

    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?

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Are the distribution database files still on your drive? (distribution.mdf and distribution.ldf)

    What does this command return when you run it?:

    select * from master.sys.databases where name = 'distribution'
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Apr 2012
    Posts
    8
    Hi

    The distribution database files are no longer on disk.

    select * from master.sys.databases where name = 'distribution'

    returns no results.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    DBCC CHECKDB(master)
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Apr 2012
    Posts
    8
    Hi : )

    DBCC results for 'master'.
    Service Broker Msg 9675, State 1: Message Types analyzed: 14.
    Service Broker Msg 9676, State 1: Service Contracts analyzed: 6.
    Service Broker Msg 9667, State 1: Services analyzed: 3.
    Service Broker Msg 9668, State 1: Service Queues analyzed: 3.
    Service Broker Msg 9669, State 1: Conversation Endpoints analyzed: 0.
    Service Broker Msg 9674, State 1: Conversation Groups analyzed: 0.
    Service Broker Msg 9670, State 1: Remote Service Bindings analyzed: 0.
    Service Broker Msg 9605, State 1: Conversation Priorities analyzed: 0.
    DBCC results for 'sys.sysrscols'.
    There are 862 rows in 12 pages for object "sys.sysrscols".
    DBCC results for 'sys.sysrowsets'.
    There are 117 rows in 1 pages for object "sys.sysrowsets".
    DBCC results for 'sys.sysallocunits'.
    There are 129 rows in 3 pages for object "sys.sysallocunits".
    DBCC results for 'sys.sysfiles1'.
    There are 2 rows in 1 pages for object "sys.sysfiles1".
    DBCC results for 'sys.syspriorities'.
    There are 0 rows in 0 pages for object "sys.syspriorities".
    DBCC results for 'sys.sysdbfrag'.
    There are 16 rows in 1 pages for object "sys.sysdbfrag".
    DBCC results for 'sys.sysfgfrag'.
    There are 2 rows in 1 pages for object "sys.sysfgfrag".
    DBCC results for 'sys.syspru'.
    There are 16 rows in 1 pages for object "sys.syspru".
    DBCC results for 'sys.sysbrickfiles'.
    There are 53 rows in 5 pages for object "sys.sysbrickfiles".
    DBCC results for 'sys.sysphfg'.
    There are 1 rows in 1 pages for object "sys.sysphfg".
    DBCC results for 'sys.sysprufiles'.
    There are 2 rows in 1 pages for object "sys.sysprufiles".
    DBCC results for 'sys.sysftinds'.
    There are 0 rows in 0 pages for object "sys.sysftinds".
    DBCC results for 'sys.sysowners'.
    There are 17 rows in 1 pages for object "sys.sysowners".
    DBCC results for 'sys.sysdbreg'.
    There are 16 rows in 1 pages for object "sys.sysdbreg".
    DBCC results for 'sys.sysprivs'.
    There are 1949 rows in 15 pages for object "sys.sysprivs".
    DBCC results for 'sys.sysschobjs'.
    There are 88 rows in 4 pages for object "sys.sysschobjs".
    DBCC results for 'sys.syslogshippers'.
    There are 0 rows in 0 pages for object "sys.syslogshippers".
    DBCC results for 'sys.syscolpars'.
    There are 702 rows in 14 pages for object "sys.syscolpars".
    DBCC results for 'sys.sysxlgns'.
    There are 40 rows in 1 pages for object "sys.sysxlgns".
    DBCC results for 'sys.sysxsrvs'.
    There are 3 rows in 1 pages for object "sys.sysxsrvs".
    DBCC results for 'sys.sysnsobjs'.
    There are 1 rows in 1 pages for object "sys.sysnsobjs".
    DBCC results for 'sys.sysusermsgs'.
    There are 0 rows in 0 pages for object "sys.sysusermsgs".
    DBCC results for 'sys.syscerts'.
    There are 6 rows in 1 pages for object "sys.syscerts".
    DBCC results for 'sys.sysrmtlgns'.
    There are 1 rows in 1 pages for object "sys.sysrmtlgns".
    DBCC results for 'sys.syslnklgns'.
    There are 3 rows in 1 pages for object "sys.syslnklgns".
    DBCC results for 'sys.sysxprops'.
    There are 0 rows in 0 pages for object "sys.sysxprops".
    DBCC results for 'sys.sysscalartypes'.
    There are 34 rows in 1 pages for object "sys.sysscalartypes".
    DBCC results for 'sys.systypedsubobjs'.
    There are 0 rows in 0 pages for object "sys.systypedsubobjs".
    DBCC results for 'sys.sysidxstats'.
    There are 188 rows in 2 pages for object "sys.sysidxstats".
    DBCC results for 'sys.sysiscols'.
    There are 342 rows in 3 pages for object "sys.sysiscols".
    DBCC results for 'sys.sysendpts'.
    There are 5 rows in 1 pages for object "sys.sysendpts".
    DBCC results for 'sys.syswebmethods'.
    There are 0 rows in 0 pages for object "sys.syswebmethods".
    DBCC results for 'sys.sysbinobjs'.
    There are 23 rows in 1 pages for object "sys.sysbinobjs".
    DBCC results for 'sys.sysaudacts'.
    There are 0 rows in 0 pages for object "sys.sysaudacts".
    DBCC results for 'sys.sysobjvalues'.
    There are 337 rows in 37 pages for object "sys.sysobjvalues".
    DBCC results for 'sys.sysclsobjs'.
    There are 22 rows in 1 pages for object "sys.sysclsobjs".
    DBCC results for 'sys.sysrowsetrefs'.
    There are 0 rows in 0 pages for object "sys.sysrowsetrefs".
    DBCC results for 'sys.sysremsvcbinds'.
    There are 0 rows in 0 pages for object "sys.sysremsvcbinds".
    DBCC results for 'sys.sysxmitqueue'.
    There are 0 rows in 0 pages for object "sys.sysxmitqueue".
    DBCC results for 'sys.sysrts'.
    There are 1 rows in 1 pages for object "sys.sysrts".
    DBCC results for 'sys.sysconvgroup'.
    There are 0 rows in 0 pages for object "sys.sysconvgroup".
    DBCC results for 'sys.sysdesend'.
    There are 0 rows in 0 pages for object "sys.sysdesend".
    DBCC results for 'sys.sysdercv'.
    There are 0 rows in 0 pages for object "sys.sysdercv".
    DBCC results for 'sys.syssingleobjrefs'.
    There are 163 rows in 1 pages for object "sys.syssingleobjrefs".
    DBCC results for 'sys.sysmultiobjrefs'.
    There are 118 rows in 1 pages for object "sys.sysmultiobjrefs".
    DBCC results for 'sys.sysguidrefs'.
    There are 1 rows in 1 pages for object "sys.sysguidrefs".
    DBCC results for 'sys.syschildinsts'.
    There are 0 rows in 0 pages for object "sys.syschildinsts".
    DBCC results for 'sys.syscompfragments'.
    There are 0 rows in 0 pages for object "sys.syscompfragments".
    DBCC results for 'sys.sysftstops'.
    There are 0 rows in 0 pages for object "sys.sysftstops".
    DBCC results for 'sys.sysqnames'.
    There are 101 rows in 1 pages for object "sys.sysqnames".
    DBCC results for 'sys.sysxmlcomponent'.
    There are 99 rows in 1 pages for object "sys.sysxmlcomponent".
    DBCC results for 'sys.sysxmlfacet'.
    There are 112 rows in 1 pages for object "sys.sysxmlfacet".
    DBCC results for 'sys.sysxmlplacement'.
    There are 18 rows in 1 pages for object "sys.sysxmlplacement".
    DBCC results for 'sys.sysobjkeycrypts'.
    There are 8 rows in 1 pages for object "sys.sysobjkeycrypts".
    DBCC results for 'sys.sysasymkeys'.
    There are 0 rows in 0 pages for object "sys.sysasymkeys".
    DBCC results for 'sys.syssqlguides'.
    There are 0 rows in 0 pages for object "sys.syssqlguides".
    DBCC results for 'sys.sysbinsubobjs'.
    There are 3 rows in 1 pages for object "sys.sysbinsubobjs".
    DBCC results for 'sys.syssoftobjrefs'.
    There are 1 rows in 1 pages for object "sys.syssoftobjrefs".
    DBCC results for 'spt_fallback_db'.
    There are 0 rows in 0 pages for object "spt_fallback_db".
    DBCC results for 'spt_fallback_dev'.
    There are 0 rows in 0 pages for object "spt_fallback_dev".
    DBCC results for 'spt_fallback_usg'.
    There are 0 rows in 0 pages for object "spt_fallback_usg".
    DBCC results for 'sys.queue_messages_1003150619'.
    There are 0 rows in 0 pages for object "sys.queue_messages_1003150619".
    DBCC results for 'sys.queue_messages_1035150733'.
    There are 0 rows in 0 pages for object "sys.queue_messages_1035150733".
    DBCC results for 'sys.queue_messages_1067150847'.
    There are 0 rows in 0 pages for object "sys.queue_messages_1067150847".
    DBCC results for 'sys.syscommittab'.
    There are 0 rows in 0 pages for object "sys.syscommittab".
    DBCC results for 'spt_monitor'.
    There are 1 rows in 1 pages for object "spt_monitor".
    DBCC results for 'spt_values'.
    There are 2506 rows in 19 pages for object "spt_values".
    DBCC results for 'MSreplication_options'.
    There are 3 rows in 1 pages for object "MSreplication_options".

    <I had to break up this post>

  6. #6
    Join Date
    Apr 2012
    Posts
    8
    <part 2>




    DBCC results for 'syspublications'.
    There are 0 rows in 0 pages for object "syspublications".
    CHECKDB found 0 allocation errors and 0 consistency errors in database 'master'.
    DBCC results for 'mssqlsystemresource'.
    Service Broker Msg 9675, State 1: Message Types analyzed: 14.
    Service Broker Msg 9676, State 1: Service Contracts analyzed: 6.
    Service Broker Msg 9667, State 1: Services analyzed: 3.
    Service Broker Msg 9668, State 1: Service Queues analyzed: 3.
    Service Broker Msg 9669, State 1: Conversation Endpoints analyzed: 0.
    Service Broker Msg 9674, State 1: Conversation Groups analyzed: 0.
    Service Broker Msg 9670, State 1: Remote Service Bindings analyzed: 0.
    Service Broker Msg 9605, State 1: Conversation Priorities analyzed: 0.
    DBCC results for 'sys.sysrscols'.
    There are 1124 rows in 11 pages for object "sys.sysrscols".
    DBCC results for 'sys.sysrowsets'.
    There are 240 rows in 2 pages for object "sys.sysrowsets".
    DBCC results for 'sys.sysallocunits'.
    There are 252 rows in 3 pages for object "sys.sysallocunits".
    DBCC results for 'sys.sysfiles1'.
    There are 2 rows in 1 pages for object "sys.sysfiles1".
    DBCC results for 'sys.syspriorities'.
    There are 0 rows in 0 pages for object "sys.syspriorities".
    DBCC results for 'sys.sysfgfrag'.
    There are 2 rows in 1 pages for object "sys.sysfgfrag".
    DBCC results for 'sys.sysphfg'.
    There are 1 rows in 1 pages for object "sys.sysphfg".
    DBCC results for 'sys.sysprufiles'.
    There are 2 rows in 1 pages for object "sys.sysprufiles".
    DBCC results for 'sys.sysftinds'.
    There are 0 rows in 0 pages for object "sys.sysftinds".
    DBCC results for 'sys.sysowners'.
    There are 14 rows in 1 pages for object "sys.sysowners".
    DBCC results for 'sys.sysprivs'.
    There are 20 rows in 1 pages for object "sys.sysprivs".
    DBCC results for 'sys.sysschobjs'.
    There are 3068 rows in 54 pages for object "sys.sysschobjs".
    DBCC results for 'sys.syscolpars'.
    There are 17990 rows in 336 pages for object "sys.syscolpars".
    DBCC results for 'sys.sysnsobjs'.
    There are 2 rows in 1 pages for object "sys.sysnsobjs".
    DBCC results for 'sys.syscerts'.
    There are 4 rows in 1 pages for object "sys.syscerts".
    DBCC results for 'sys.sysxprops'.
    There are 0 rows in 0 pages for object "sys.sysxprops".
    DBCC results for 'sys.sysscalartypes'.
    There are 34 rows in 1 pages for object "sys.sysscalartypes".
    DBCC results for 'sys.systypedsubobjs'.
    There are 0 rows in 0 pages for object "sys.systypedsubobjs".
    DBCC results for 'sys.sysidxstats'.
    There are 540 rows in 10 pages for object "sys.sysidxstats".
    DBCC results for 'sys.sysiscols'.
    There are 808 rows in 5 pages for object "sys.sysiscols".
    DBCC results for 'sys.sysbinobjs'.
    There are 23 rows in 1 pages for object "sys.sysbinobjs".
    DBCC results for 'sys.sysaudacts'.
    There are 0 rows in 0 pages for object "sys.sysaudacts".
    DBCC results for 'sys.sysobjvalues'.
    There are 3735 rows in 1279 pages for object "sys.sysobjvalues".
    DBCC results for 'sys.sysclsobjs'.
    There are 20 rows in 1 pages for object "sys.sysclsobjs".
    DBCC results for 'sys.sysrowsetrefs'.
    There are 0 rows in 0 pages for object "sys.sysrowsetrefs".
    DBCC results for 'sys.sysremsvcbinds'.
    There are 0 rows in 0 pages for object "sys.sysremsvcbinds".
    DBCC results for 'sys.sysxmitqueue'.
    There are 0 rows in 0 pages for object "sys.sysxmitqueue".
    DBCC results for 'sys.sysrts'.
    There are 1 rows in 1 pages for object "sys.sysrts".
    DBCC results for 'sys.sysconvgroup'.
    There are 0 rows in 0 pages for object "sys.sysconvgroup".
    DBCC results for 'sys.sysdesend'.
    There are 0 rows in 0 pages for object "sys.sysdesend".
    DBCC results for 'sys.sysdercv'.
    There are 0 rows in 0 pages for object "sys.sysdercv".
    DBCC results for 'sys.syssingleobjrefs'.
    There are 895 rows in 6 pages for object "sys.syssingleobjrefs".
    DBCC results for 'sys.sysmultiobjrefs'.
    There are 486 rows in 2 pages for object "sys.sysmultiobjrefs".
    DBCC results for 'sys.sysguidrefs'.
    There are 3 rows in 1 pages for object "sys.sysguidrefs".
    DBCC results for 'sys.syscompfragments'.
    There are 0 rows in 0 pages for object "sys.syscompfragments".
    DBCC results for 'sys.sysftstops'.
    There are 15829 rows in 119 pages for object "sys.sysftstops".
    DBCC results for 'sys.sysqnames'.
    There are 483 rows in 4 pages for object "sys.sysqnames".
    DBCC results for 'sys.sysxmlcomponent'.
    There are 736 rows in 4 pages for object "sys.sysxmlcomponent".
    DBCC results for 'sys.sysxmlfacet'.
    There are 322 rows in 2 pages for object "sys.sysxmlfacet".
    DBCC results for 'sys.sysxmlplacement'.
    There are 923 rows in 7 pages for object "sys.sysxmlplacement".
    DBCC results for 'sys.sysobjkeycrypts'.
    There are 848 rows in 26 pages for object "sys.sysobjkeycrypts".
    DBCC results for 'sys.sysasymkeys'.
    There are 0 rows in 0 pages for object "sys.sysasymkeys".
    DBCC results for 'sys.syssqlguides'.
    There are 0 rows in 0 pages for object "sys.syssqlguides".
    DBCC results for 'sys.sysbinsubobjs'.
    There are 3 rows in 1 pages for object "sys.sysbinsubobjs".
    DBCC results for 'sys.syssoftobjrefs'.
    There are 0 rows in 0 pages for object "sys.syssoftobjrefs".
    DBCC results for 'sys.syspalvalues'.
    There are 598 rows in 5 pages for object "sys.syspalvalues".
    DBCC results for 'sys.spt_server_info'.
    There are 27 rows in 1 pages for object "sys.spt_server_info".
    DBCC results for 'sys.spt_datatype_info'.
    There are 76 rows in 1 pages for object "sys.spt_datatype_info".
    DBCC results for 'sys.role_permissions'.
    There are 165 rows in 2 pages for object "sys.role_permissions".
    DBCC results for 'sys.sysbinpals'.
    There are 45 rows in 1 pages for object "sys.sysbinpals".
    DBCC results for 'sys.syscolrdb'.
    There are 11905 rows in 183 pages for object "sys.syscolrdb".
    DBCC results for 'sys.spt_provider_types'.
    There are 32 rows in 1 pages for object "sys.spt_provider_types".
    DBCC results for 'sys.syspalnames'.
    There are 127 rows in 1 pages for object "sys.syspalnames".
    DBCC results for 'sys.spt_permission_names'.
    There are 5 rows in 1 pages for object "sys.spt_permission_names".
    DBCC results for 'sys.sysobjrdb'.
    There are 1925 rows in 27 pages for object "sys.sysobjrdb".
    DBCC results for 'sys.spt_datatype_info_ext'.
    There are 13 rows in 1 pages for object "sys.spt_datatype_info_ext".
    DBCC results for 'sys.queue_messages_1977058079'.
    There are 0 rows in 0 pages for object "sys.queue_messages_1977058079".
    DBCC results for 'sys.queue_messages_2009058193'.
    There are 0 rows in 0 pages for object "sys.queue_messages_2009058193".
    DBCC results for 'sys.queue_messages_2041058307'.
    There are 0 rows in 0 pages for object "sys.queue_messages_2041058307".
    DBCC results for 'sys.filestream_tombstone_2073058421'.
    There are 0 rows in 0 pages for object "sys.filestream_tombstone_2073058421".
    DBCC results for 'sys.syscommittab'.
    There are 0 rows in 0 pages for object "sys.syscommittab".
    CHECKDB found 0 allocation errors and 0 consistency errors in database 'mssqlsystemresource'.
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I think you are seeing a conflict between a registry key, and the actual list of databases. Try this. Create a database called distribution by just running
    Code:
    CREATE DATABASE distribution
    Then go back, and see if you can disable publishing (my guess is that you are going to get some error about tables not existing, but hey, it's progress...of a sort). The alternative is to scan through the MSSQLServer sections of HKEY_LOCAL_MACHINE and seeing if you can guess which wire to cut. If all else fails, toss the server in a box with a radioactive isotope.

  8. #8
    Join Date
    Apr 2012
    Posts
    8
    So the good news is, I'm an idiot. : ) 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?

  9. #9
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Sounds like a lot of manual deleting was done, so you may want to take this opportunity to disable/delete publishing from the server, drop the distribution database, and create a brand new one. Assuming there is nothing you need in the old distribution database, of course.

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd suggest dropping replication altogether, then disable replication (everything) and reboot the publisher/distributor. After the reboot, I'd drop the publication database if it isn't cleaned up for you.

    Once you get back to a clean slate, then I'd reinstall and begin the process again.You may want to consider some of the advanced replication scenarios covered in SQL Server Replication

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  11. #11
    Join Date
    Apr 2012
    Posts
    8
    Hi

    How do I drop & recreate the distribution database? I've found to my chagrin that simply dropping the db from tsql makes sql server unhappy.

  12. #12
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    service broker
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  13. #13
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    By disabling publication first. There will likely be a checkbox for drop distribution database along the way.

  14. #14
    Join Date
    Apr 2012
    Posts
    8
    Hmm.. I've opened a support case with MS for this. I'll update with any info.

  15. #15
    Join Date
    Apr 2012
    Posts
    8
    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'

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •