4

We recently retired a replication publication, deleting each subscription (push subscriptions) and then deleting the publication, all from within SSMS. However, we continue to get the following message (once every minute) in the four days since deleting this publication.

Replication-Replication Distribution Subsystem: agent (null) failed. The publication 'Replicate_ASG_Reference_Data' does not exist.

Why is it still trying to run a deleted publication, and how do I stop it?

Additional information: This is on SQL Server 2008 SP3 running on Windows Server 2008 R2.

5 Answers 5

2

It looks like you have some orphaned replication bits and/or orphaned replication agent jobs.

Note that Distribution.dbo.sp_MSremove_published_jobs will not remove your Distribution Agent jobs. You will have to manually delete the orphaned job(s).

Locate the job(s) under SQL Server Agent -> Jobs. The Distribution Agent job names will have the format Publisher-PublicationDatabase-Publication-Subscriber-integer. Right-click the job -> Delete.

I hope this helps.

2
  • Unfortunately, we had three replication publications (2 snapshot, one transactional) on the database; we removed one (snapshot replication) but the other two continue in use. It's a non-trivial matter, operationally and procedurally (business-wise) to remove the database from replication. Sep 25, 2013 at 19:03
  • Then just manually delete the orphaned Distribution Agent job(s). That will clear up your error. Sep 25, 2013 at 20:07
2

Funny answer to this one for me. One of the subscribers was still attempting to connect to this publication. Once I found it, I deleted it and all was good in the hood.

1

You have to properly clean up replication using :

sp_removedbreplication 'database_name' 

Since you dont need replication, you can delete the associated jobs as well on the distribution server.


EDIT : Since you have to remove snapshot replication only from the current database which is being used for T-REP as well, follow below steps :

At publisher database :

sp_droppublication  @publication=  'publication_to_Drop' 

At subscriber datatabase :

sp_subscription_cleanup [ @publisher = ] 'publisher'
        , [ @publisher_db = ] 'publisher_db'
    [ , [ @publication = ] 'publication']

Refer to kb324401


Manually or using T-SQL :

exec Distribution.dbo.sp_MSremove_published_jobs @server = 'PublicationServer', 
@database = 'replicated_DB_NAME'

Also refer to : Steps to clean up orphaned replication settings in SQL Server and How to cleanup Replication Bits

2
  • Unfortunately, we had three replication publications (2 snapshot, one transactional) on the database; we removed one (snapshot replication) but the other two continue in use. It's a non-trivial matter, operationally and procedurally (business-wise) to remove the database from replication. Sep 25, 2013 at 19:00
  • Then you have to clean up replication bits for snapshot replication ONLY as I have pointed out.
    – Kin Shah
    Sep 25, 2013 at 19:05
0

This issue never resolved. We dealt with this until migrating to SQL 2012 in October 2013.

0
0

Same problem here, this is how I solved it

  1. find running job NAME with this somewhere in command 'Replicate_ASG_Reference_Data' use this SQL:

    USE master
    GO
    SELECT job.job_id,name,command
    FROM msdb.dbo.sysjobs job 
      INNER JOIN msdb.dbo.sysjobsteps steps ON job.job_id = steps.job_id
    WHERE job.enabled = 1 AND command LIKE '%ASG%'
    
  2. this SQL will show 1 or more jobs which have this in command, find offending one and disable/delete job

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.