February 8, 2010 at 2:39 pm
Hey all,
I am having issues re-establishing replication between two servers after restoring the replicating databases from backups. One issue I had was with "ghost" subscriptions (i.e. ones that can't be deleted in Enterprise Manager, and that are present even though there are no publications present).
One way I've dealt with problems like that in the past has been to use sp_removedbreplication. I've found that this usually gets rid of any subscriptions that are still hanging around. However, when attempting to re-add publications for the databases (for transactional replication), I was receiving the error: "Invalid object name 'syssubscriptions'" on one of the databases (but not the other).
One database server has a "distribution" database, and the other doesn't. I finally got around the syssubscriptions error using the GUI in Enterprise Manager, but now I'm getting "Invalid object name 'syspublications'" when trying to re-add subscriptions for transactional replication between the two databases.
I have a few questions:
1. How can I deal with the syspublications error? Is there some way to re-add it to the database from which it is missing?
2. Where are the syssubscriptions and syspublications tables located? Even on the server where I wasn't getting the syssubscriptions error, I couldn't find the table anywhere, including in the system tables in master and in the user database.
3. Is the source of these problems my usage of sp_removedbreplication? It has worked for removing vestigial subscriptions for me in the past, but I get the sense that it may be too heavy-handed solution for my problem. Does anyone know of some better tools for listing/deleting publications and subscriptions using T-SQL rather than Enterprise Manager (e.g. sp_replicationdboption)?
I would greatly appreciate any advice that anyone can provide.
February 8, 2010 at 6:09 pm
I realize that no one replied to the threat yet, but I wanted to share some information I have found out in addressing this problem in hopes that it'll be useful to someone in the future.
I was able to get rid of the vestigial publications by using sp_subscription_cleanup.
The tables syssubscriptions and syspublications are under User Tables. I'm not sure why they are appearing here.
I am not sure how I got around the "invalid object" errors for syssubscriptions and (later) syspublications, but it involved playing around with settings in the GUI in Enterprise Manager (i.e. using the menu choices "Configure Publishing, Subscribers, and Distribution..." and "Disable Publishing and Distribution..." from the context menu that appears when one right-clicks on Replication under each server in Enterprise Manager).
February 10, 2010 at 2:06 pm
This might help in identifying where replication tables are hosted
Joie Andrew
"Since 1982"
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply