Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

5 Answers 5

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.

share|improve this answer

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

share|improve this answer
    
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. –  KRISTOPHER COOK Sep 25 '13 at 19:00
    
Then you have to clean up replication bits for snapshot replication ONLY as I have pointed out. –  Kin Sep 25 '13 at 19:05

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.

share|improve this answer
    
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. –  KRISTOPHER COOK Sep 25 '13 at 19:03
    
Then just manually delete the orphaned Distribution Agent job(s). That will clear up your error. –  Brandon Williams Sep 25 '13 at 20:07
up vote 0 down vote accepted

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

share|improve this answer

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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