Archive

Archive for August, 2011

Cleaning up non-existing publications from Replication Manager

August 24, 2011 Leave a comment

Cleaning up non-existing publications from Replication Manager

Couple of days ago I found an interesting fact: not always deleted publications disappear from Replication Manager.

SSMS:

Replication Monitor:

Searching for hints came around this SQL Central forum thread which is kind of old and goes for SQL Server 7/2000. Well, as I figured out myself, anything mentioned there is totally valid for SS2008 (and although my fix was done on SS2008, I’m pretty sure it would be the same for SS2005).

In short, in a situation when Replication Manager has a bold red cross and there are some publications which had been dropped previously, the solution is to re-create those publications, with the same name, and then drop it. This will remove the corresponding entries from Replication Manager.

I took the step aside from what the majority of post authors were doing, and first cleaned the replication data dictionary from the orphan publication records. I thought this along would solve the problem. Nope, it doesn’t. Bit still, it’s good to make sure a Data Dictionary of any kind, including Replication, doesn’t contain garbage, so I believe it’s worthwhile being done anyway.

BEGIN TRAN

DELETE FROM MSpublications
WHERE publisher_db = ‘WebFactory’
AND publication_id BETWEEN 49 AND 57;

DELETE FROM MSsubscriptions
WHERE publisher_db = ‘WebFactory’
AND publication_id BETWEEN 49 AND 57;

DELETE FROM MSReplication_Monitordata
WHERE publisher_db = ‘WebFactory’ ;

DELETE FROM MSlogreader_agents
WHERE publisher_db = ‘WebFactory’;

DELETE FROM dbo.MSarticles
WHERE publisher_db = ‘WebFactory’;

DELETE FROM dbo.MSdistribution_agents
WHERE publisher_db = ‘WebFactory’;

DELETE FROM MSdistribution_history
WHERE CONVERT(VARCHAR, time, 102) = CONVERT(VARCHAR, GETDATE(), 102);

DELETE FROM dbo.MSpublication_access
WHERE publication_id BETWEEN 49 AND 57;

DELETE FROM MSpublicationthresholds
WHERE publication_id BETWEEN 49 AND 57;

DELETE FROM dbo.MSpublisher_databases
WHERE publisher_db = ‘WebFactory’;

DELETE FROM dbo.MSqreader_history;

DELETE FROM dbo.MSreplication_monitordata
WHERE publisher_db = ‘WebFactory’;

COMMIT TRAN

After that, I re-created the publications minding their names, and then dropped them.

Finally, Voilà:

As you can see,¬†Replication Monitor doesn’t display “ghost” publications anymore.