SQL Replication Issues

Question

Post reply

SQL Replication Issues

Radders

Old Hand

Points: 384
More actions
June 7, 2017 at 8:25 am

#330864

Hi All,
I have a situation whereby our replication failed recently. On investigation I have found the results from a 'SP_HelpDistributor' query on the distributor instance, brings back the all blank fields other than the 'rpc server name' (REPL_DSTRIBUTOR).
In trying to remove the replication to start again I cannot as this distributor has now been orphaned and I am getting messages as per the image below.
I have tried sp_dropdistpublisher and many other methods on ssms to delete the replication and publisher to no avail as according to SQL the distributor does not exist
Not only that, I cannot change the distributor to a freshly created one on a new instance either!
Can anyone please help????

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

shahm10 SSCrazy Points: 2217 More actions · Answer 1

What error message did you get when executed sp_dropdistpublisher?

Sue_H SSC Guru Points: 90860 More actions · Answer 2

What if anything did you drop so far? Publications, distribution database?
What do you get when you execute this on the distributor instance:
EXEC sp_get_distributor
And what do you get when you execute the following on the instance:

SELECT name, data_source 
FROM sys.servers
WHERE is_distributor = 1

Sue

Sue_H SSC Guru Points: 90860 More actions · Answer 3

And one other one to check - what do you get when you execute the following:
SELECT @@SERVERNAME SELECT SERVERPROPERTY('MachineName') SELECT SERVERPROPERTY('ServerName')

Sue

Radders Old Hand Points: 384 More actions · Answer 4

shahm10,
I am getting the following when trying to drop the distributor:

Msg 14071, Level 16, State 1, Procedure sp_dropdistpublisher, Line 110
Could not find the Distributor or the distribution database for the local server.
The Distributor may not be installed, or the local server may not be configured as a Publisher at the Distributor.

Sue_H,
I am getting the following from your queries (I have to remove the real names of course):

My Distribution Server is a sql instance on the Subscription Server

Radders Old Hand Points: 384 More actions · Answer 5

Radders

Old Hand

Points: 384

June 9, 2017 at 9:41 am

#1946202

Any takers yet??
thanks

Sue_H SSC Guru Points: 90860 More actions · Answer 6

Radders - Friday, June 9, 2017 9:41 AM
Any takers yet??
thanks

Do you have a distribution database on the instance? Do you have a linked server for named repl_distributor?

What exactly did you do in removing replication - what steps did you take? Did you remove subscriptions? Did you remove publications?
Was this server only a distributor? Were any other servers using this instance as a distributor?
Did you run the rest of the replication sp_helpxxxx stored procedures?
EXEC sp_helpdistributor;
EXEC sp_helpdistributiondb;
EXEC sp_helpdistpublisher;
EXEC sp_helpreplicationdboption;

Sue

Radders Old Hand Points: 384 More actions · Answer 7

Sue_H - Friday, June 9, 2017 10:19 AM
Radders - Friday, June 9, 2017 9:41 AM
Any takers yet??
thanks
Do you have a distribution database on the instance? Do you have a linked server for named repl_distributor?
What exactly did you do in removing replication - what steps did you take? Did you remove subscriptions? Did you remove publications?
Was this server only a distributor? Were any other servers using this instance as a distributor?
Did you run the rest of the replication sp_helpxxxx stored procedures?
EXEC sp_helpdistributor;
EXEC sp_helpdistributiondb;
EXEC sp_helpdistpublisher;
EXEC sp_helpreplicationdboption;
Sue

Hi Sue,
In answer to your questions:
Yes there is a Distribution DB
Yes there is a linked server for named repl_distributor
I tried to delete the replication in the correct order however the due to the blank fields on the distribution instance

I cannot. (Is this the reason for the failures??)
No other servers using this instance.
Ran all the sp_helpxxxx sprocs too:
Exec sp_helpdistributor... empty table other than 'rpc server name - REPL_DISTRIBUTOR'
Exec sp_helpdistributiondb... The server 'XXXXXXXX\DISTRIBUTION' is not configured as a Distributor.
Exec sp_helpdistpublisher... completed sucessfully
Exec sp_helpreplicationdboption... completed but empty results
I tried to use the 'configure distributor' wizard but get back 'the server 'Null' is already defined as a distributor'

I get the feeling the 'Null' is the problem as I created another Distributor instance and built replication to the same Publisher. The table was indeed populated with the correct server names. I cannot use this Distributor however as the Publisher will not let me change for the current one...Arghh!
thanks for you assistance so far

Sue_H SSC Guru Points: 90860 More actions · Answer 8

The problem looks like a couple of things - looks like that main one could be that there is a publisher that has a dependency on that distributor.
The null you are getting is part of the problem - you don't have all of the information in sys.servers but you would want to look at the publications, subscriptions first before trying to clean that up.
If you are trying to rebuild your replication, you would normally remove the publications as well but it looks like that never happened. Not sure about the subscriptions.
Try running the following in your distribution database to get information on the subscriber, publisher for your problematic distributor:
select * from dbo.MSpublications select * from dbo.MSsubscriptions

Sue

Radders Old Hand Points: 384 More actions · Answer 9

Hi All,
I managed to find the problem in the end.
Sue H .. there was indeed another dependency on the replication (another orphaned replica job)
Once I deleted that one I could drop the subscriptions and recreate the replication.
This was inherited from a previous DBA and not documented...doh!

Thank you all for your help on this one, I can breathe easy now

Sue_H SSC Guru Points: 90860 More actions · Answer 10

Radders - Sunday, July 2, 2017 7:00 PM
Hi All,
I managed to find the problem in the end.
Sue H .. there was indeed another dependency on the replication (another orphaned replica job)
Once I deleted that one I could drop the subscriptions and recreate the replication.
This was inherited from a previous DBA and not documented...doh!
Thank you all for your help on this one, I can breathe easy now

No problem....thanks for posting back! Glad it's all good now.

Sue