| Author |
 Topic  |
|
|
garetht
Starting Member
5 Posts |
 Posted - 04/05/2012 : 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
USA
5037 Posts |
Posted - 04/05/2012 : 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 - 04/05/2012 : 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
USA
5037 Posts |
Posted - 04/05/2012 : 13:51:00
|
| what happens when you execute sp_removedbreplication at the publisher? |
|
|
|
garetht
Starting Member
5 Posts |
Posted - 04/05/2012 : 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 - 04/05/2012 : 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
USA
5037 Posts |
Posted - 04/05/2012 : 18:50:30
|
| let it finish |
|
|
|
garetht
Starting Member
5 Posts |
Posted - 04/09/2012 : 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'
|
|
|
| |
Topic |
|
Schroedinger's distribution database
