March 28, 2008 at 5:56 am
Hi guys,
Having a few issues with a Snapshot Replication running between 2 SQL 2005 Servers (Standard Edition).
OK, last week I created a snapshot replication for a database on the primary server that was replicated to the secondary server at preset intervals.
After using this for a few days the database was to be changed drastically, so we no longer needed the replication.
So, from the subscriber we deleted the subscription and when prompted, deleted the publication on the primary server.
So far so good.
On Wednesday the database went live again (same DB name), so I created a new replication for it as before, but renamed the replication to something different.
Today I launched Replication Monitor and I see the new replication is working fine, but it now has the previously deleted one in the list with a red cross through it with 'Error' against it, and 'not running'.
I deleted it's instance in the REPLDATA folder too, but the replication monitor still shows old one with a fault.
Question is, how do I remove it completely so it no longer appears in Replication Monitor? This is the only time this has happened, and I followed the same procedures as before, but obviously something didn't go right this time.
Any ideas?
Thanks all,
TTG
Experience is something you get just after you need it...
March 31, 2008 at 11:22 pm
This is probably a rouge job. I have had this happen several times. Look in the activity monitor and filter on that name to make it easier. See if the job is still there, if so delete it. Sometimes you have to go through the system tables on the distribution server and remove these as well. I have a script with all the tables I usually go through manually to remove this type of thing because usually there is leftovers else where too.
Once you get rid of it as I suspect in the jobs it should not show up in the SQL monitor, but if it does then just look at your system tables for that job as well. I posted the script below I use as my template. It should give you an idea of what you need to do.
-- Get rid of publication based on name
SELECT [id]
,[name]
,[publisher_id]
,[publisher_db]
,[publication]
,[subscriber_id]
,[subscriber_db]
,[job_id]
FROM [distribution].[dbo].[MSdistribution_agents]
WHERE publication = 'PublicationName'
-- WHERE name = 'JOB_NAME_IN_QUESTON'
-- Find the correct publication to delete
SELECT [publisher_id]
,[publisher_db]
,[publication]
,[publication_id]
FROM [distribution].[dbo].[MSpublications]
WHERE publication = 'PublicationName'
-- WHERE Publication_id in (5)
-- GET PUBLICATION ID OF THE ARTICLE TO DROP
SELECT [publisher_id]
,[publisher_db]
,[publication_id]
,[article]
,[article_id]
FROM [distribution].[dbo].[MSarticles]
WHERE Publication_id in (5)
-- Delete the record that matches the name of the snapshot for the publication
SELECT [id]
,[name]
,[publisher_id]
,[publisher_db]
,[publication]
FROM [distribution].[dbo].[MSsnapshot_agents]
WHERE publication = 'PublicationName'
-- remove subscriptions for the publication
SELECT [publisher_database_id]
,[publisher_id]
,[publisher_db]
,[publication_id]
,[article_id]
FROM [distribution].[dbo].[MSsubscriptions]
WHERE Publication_id in (5)
-- RUN ON SUBSCRIBER DATABASE
-- Remove record for publication if it exists
SELECT [publisher]
,[publisher_db]
,[publication]
,[independent_agent]
,[subscription_type]
,[distribution_agent]
FROM [dbo].[MSreplication_subscriptions]
WHERE publisher = 'FNFSSQLSRV2'
and publication = 'PublicationName'
SELECT [id]
,[publisher]
,[publisher_db]
,[publication]
FROM [dbo].[MSsubscription_agents]
WHERE publisher = 'FNFSSQLSRV2'
and publication = 'PublicationName'
April 1, 2008 at 7:23 am
Thanks for the reply Mikelyn!
I'll take a look through the server today and follow your advice.
I'm pretty new to SQL admin, so may take me some time 🙂
Thanks again,
TTG
Experience is something you get just after you need it...
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply