May 24, 2002 at 9:20 am
I hope somebody can help. I've got a problem removing replication from a server. A client of ours was having some trouble w/ replication and tried to remove it from the server. Something went wrong (no one is quite sure what), but the distribution database remains, and refuses to be dropped because it thinks it is still participating in replication. As you can see below, the publication no longer exists. But now we can't get replication rebuilt. It's stuck in a sort of "almost there, but not quite" state.
We ran the following script:
use [DESSP001]
GO
-- Dropping the merge publication
exec sp_dropmergepublication @publication = DESSP001_Pub'
GO
-- Disabling the replication database
use master
GO
exec sp_replicationdboption @dbname = N'DESSP001', @optname = N'merge
publish', @value = N'false'
GO
use master
GO
exec sp_dropdistributor @no_checks = 1
GO
And got the following:
Server: Msg 20026, Level 16, State 1, Procedure sp_dropmergepublication, Line 94
The publication 'DESSP001_Pub' does not exist.
Server: Msg 20029, Level 16, State 1, Procedure sp_MSmergepublishdb, Line 55
The Distributor has not been installed correctly. Could not disable database for publishing.
Server: Msg 20029, Level 16, State 1, Procedure sp_MSmergepublishdb, Line 55
The Distributor has not been installed correctly. Could not disable database for publishing.
I've checked serveral sources, no one seems to have a clue. The only thing that I found that even came close was a suggestion to open a call to MS Support Center.
I'm a developer, not a DBA and I feel like the blind leading the blind.
Any help would be appreciated.
May 24, 2002 at 6:16 pm
Sp_replicationdboption supports @ignore_distributor as a param, I'd give that a try. Sp_dropdistributor has a similar switch. See if either of those help, let us know what happens.
Andy
August 4, 2008 at 9:13 am
Can't say if it worked for them but it worked for me!
-- first tell repl it can let go of what it has already saved
exec sp_repldone
@xactid = NULL
,@xact_segno = NULL
,@numtrans = 0
,@time = 0
,@reset = 1
-- then do a manual thwack of the log
backup log [yourdbnamehere] with truncate_only
-- then tell replication its services are no longer required
exec sp_replicationdboption
@dbname = '[yourdbnamehere] '
,@optname = 'publish'
,@value = 'false'
,@ignore_distributor = 1
go
Thanks muchly.
August 6, 2008 at 4:26 am
just disable replication & then drop subscriber
August 6, 2008 at 2:16 pm
My case was coming from restoring a database backup of a replicated database onto a system that wasn't using replication- there were no subscribers to drop, no replication to disable.
November 27, 2008 at 3:03 pm
Runing Larry's script in the database that I was trying to replicate allowed me to run sp_dropdistributor without having the annoying error message.
February 11, 2009 at 8:11 pm
Aaron Gonzalez (11/27/2008)
Runing Larry's script in the database that I was trying to replicate allowed me to run sp_dropdistributor without having the annoying error message.
Yes - me too, thought I was never going to get rid of that damn error message. Nice one Larry
May 4, 2009 at 4:08 pm
You are going to truncate production database log in order to drop distribution database???
May 6, 2009 at 12:39 pm
Different options:
Option 1
disable publishing then drop the distributor.
ref: http://msdn.microsoft.com/en-us/library/ms147921.aspx
Option 2
running xp_repldone with appropriate parameters:
Larry G's SQL syntax to run wherever distribution db is:
-- first tell repl it can let go of what it has already saved
exec sp_repldone
@xactid = NULL
,@xact_segno = NULL
,@numtrans = 0
,@time = 0
,@reset = 1
-- then do a manual thwack of the log
backup log [yourdbnamehere] with truncate_only
-- then tell replication its services are no longer required
exec sp_replicationdboption
@dbname = '[yourdbnamehere] '
,@optname = 'publish'
,@value = 'false'
,@ignore_distributor = 1
go
Option 3:
sp_configure 'show advanced options', 1
reconfigure with override
go
sp_configure 'Allow Updates', 1
reconfigure with override
go
UPDATE sysdatabase
SET category = 0
WHERE name = 'distribution'
sp_configure 'show advanced options', 0
reconfigure with override
go
sp_configure 'Allow Updates', 0
reconfigure with override
go
run the following after applying the Option 3:
-- Delete the distribution database.
EXEC sp_dropdistributiondb @distributionDB;
-- Remove the local server as a Distributor.
EXEC sp_dropdistributor;
GO
hope this helps!
May 6, 2009 at 2:32 pm
>You are going to truncate production database log in order to drop distribution database????
No- a development database log. =)
FWIW- I have never actually needed to apply a transaction log backup since they were called "after image journals" in Rdb 2.0 which might speak to my cavalier attitude in occasionally making a choice to whack the log in a production system if it's hung and whacking the log will fix it; the context of the problem specifies the reasonable solutions.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply