Schroedinger's distribution database

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Replication (2008)

Schroedinger's distribution database

Author

Topic

garetht
Starting Member

5 Posts

Posted - 2012-04-05 : 01:15:58

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. (http://weblogs.sqlteam.com/tarad/archive/2009/09/02/How-to-remove-a-SQL-Server-distribution-database-if-other.aspx)

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?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2012-04-05 : 12:30:34

Distributor is on the same machine as publisher?

What does this return?
SELECT * FROM sys.databases WHERE name = 'distribution';

garetht
Starting Member

5 Posts

Posted - 2012-04-05 : 12:59:38

Hi

Yes, Distributor and Publisher are the same server.

Running
SELECT * FROM sys.databases WHERE name = 'distribution';

returns no results.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2012-04-05 : 13:51:00

what happens when you execute sp_removedbreplication at the publisher?

garetht
Starting Member

5 Posts

Posted - 2012-04-05 : 14:33:22

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?

garetht
Starting Member

5 Posts

Posted - 2012-04-05 : 18:45:33

Hmm.. I've opened a support case with MS for this. I'll update with any info.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2012-04-05 : 18:50:30

let it finish

garetht
Starting Member

5 Posts

Posted - 2012-04-09 : 13:39:01

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'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources