this is what I normally do when I have a publication that is messed up.

it is a bit ugly but it has worked for me on many occasions on different environments. what causes it? that is sometimes difficult to figure it out, best sometimes is to start from scratch, but even for that you need to clear up `all` the `residuals` from the current publication that is faulty.


just to put it into context:

This is what I see from the replication monitor:

[![enter image description here][1]][1]

and when I use [my own replication monitor using T-SQL][2]:


    DECLARE @cmd NVARCHAR(max)
    DECLARE @publisher SYSNAME, @publisher_db SYSNAME, @publication SYSNAME, @pubtype INT
    DECLARE @subscriber SYSNAME, @subscriber_db SYSNAME, @subtype INT
    DECLARE @cmdcount INT, @processtime INT
    DECLARE @ParmDefinition NVARCHAR(500)
    DECLARE @JobName SYSNAME
    DECLARE @minutes INT, @threshold INT, @maxCommands INT, @mail CHAR(1) = 'N'
    SET @minutes = 60 --> Define how many minutes latency before you would like to be notified
    SET @maxCommands = 80000  --->  change this to represent the max number of outstanding commands to be proceduresed before notification
    SET @threshold = @minutes * 60
    
    IF OBJECT_ID ('TEMPDB..#Replication_Qu_History')  IS NOT NULL
       DROP TABLE #Replication_Qu_History
    
    IF OBJECT_ID ('TEMPDB..##PublicationInfo')  IS NOT NULL
       DROP TABLE  ##PublicationInfo
    
    IF OBJECT_ID ('TEMPDB..#PublisherInfo')  IS NOT NULL
       DROP TABLE  #PublisherInfo
    
    IF OBJECT_ID ('TEMPDB..##SubscriptionInfo')  IS NOT NULL
       DROP TABLE  ##SubscriptionInfo
                   
    SELECT * INTO #PublisherInfo
    FROM OPENROWSET('SQLOLEDB', 'SERVER=(LOCAL);TRUSTED_CONNECTION=YES;'
    , 'SET FMTONLY OFF EXEC distribution.dbo.sp_replmonitorhelppublisher')
    
    SELECT @publisher = publisher FROM #PublisherInfo     
    
    SET @cmd = 'SELECT * INTO ##PublicationInfo FROM OPENROWSET(''SQLOLEDB'',''SERVER=(LOCAL);TRUSTED_CONNECTION=YES''
    ,''SET FMTONLY OFF EXEC distribution.dbo.sp_replmonitorhelppublication @publisher='
    + @publisher + ''')'
    --select @cmd
    EXEC sp_executesql @cmd
    
    SELECT @publisher_db=publisher_db, @publication=publication, @pubtype=publication_type  FROM ##PublicationInfo
    
    SET @cmd = 'SELECT * INTO ##SubscriptionInfo FROM OPENROWSET(''SQLOLEDB'',''SERVER=(LOCAL);TRUSTED_CONNECTION=YES''
    ,''SET FMTONLY OFF EXEC distribution.dbo.sp_replmonitorhelpsubscription @publisher='
    + @publisher + ',@publication_type=' + CONVERT(CHAR(1),@pubtype) + ''')'
    --select @cmd
    EXEC sp_executesql @cmd
    
    
    ALTER TABLE ##SubscriptionInfo
    ADD  PendingCmdCount INT NULL,
    EstimatedProcessTime INT NULL
    
    
    SELECT *
    FROM #PublisherInfo
    
    SELECT *
    FROM ##SubscriptionInfo 
    
    SELECT *
    FROM ##PublicationInfo 

you can see 2 lines on the last box below - and one one them should not be there:

[![enter image description here][3]][3]

the same when I use this script:

    EXEC distribution.dbo.sp_replmonitorhelppublication @publisher='my publisher'

[![enter image description here][4]][4]


First you do what is shown in the other [answers][5] above, [if that works][6], that is fine, it sometimes works, problem solved.

that would be more of less it:


    exec master.dbo.sp_replicationdboption @dbname = 'my_PUBLICATION', @optname = N'publish', @value = N'false'
    exec master.dbo.sp_replicationdboption @dbname = 'my_PUBLICATION', @optname = N'publish', @value = N'true'
    
    sp_droppublication @publication='my_PUBLICATION'
    
    -- Remove replication objects from a subscription database (if necessary).
    exec master.dbo.sp_removedbreplication 'my_PUBLICATION'
    
    exec master.dbo.sp_removedbreplication 'my_PUBLICATION'
    
    use my_PUBLICATION
    
    sp_removedbreplication @type='both'
    
    
    USE [master]
    EXEC sp_replicationdboption 
      @dbname = N'my_PUBLICATION', 
      @optname = N'publish', 
      @value = N'false';
    GO
    
    
    EXEC distribution.dbo.sp_replmonitorhelppublication @publisher='PUBLISHER_SERVER'
    
    sp_replmonitorhelppublisher @publisher='PUBLISHER_SERVER'
    
    DECLARE @publicationDB AS sysname;
    DECLARE @publication AS sysname;
    SET @publicationDB = N'my_PUBLICATION'; 
    SET @publication = N'my_PUBLICATION'; 
    
    -- Remove a transactional publication.
    USE my_PUBLICATION
    EXEC sp_droppublication @publication = @publication;
    
    -- Remove replication objects from the database.
    USE [master]
    EXEC sp_replicationdboption 
      @dbname = @publicationDB, 
      @optname = N'publish', 
      @value = N'false';
    GO






Now to get rid completely of this publication we will start by connection to the buscriber, and then publisher, and then distributor as per the script below:


    -- Connect Subscriber
    :connect [SUBSCRIBER_SERVER]
    use [master]
    exec sp_helpreplicationdboption @dbname = N'SUBSCRIBER_DATABASE'
    go
    use [SUBSCRIBER_DATABASE]
    exec sp_subscription_cleanup @publisher = N'PUBLISHER_SERVER', @publisher_db = N'my_PUBLICATION_DB', 
    @publication = N'my_PUBLICATION'
    go
    
    
    -- Connect Publisher Server
    :connect [PUBLISHER_SERVER]
    -- Drop Subscription
    use [my_PUBLICATION]
    exec sp_dropsubscription @publication = N'my_PUBLICATION', @subscriber = N'all', 
    @destination_db = N'SUBSCRIBER_DATABASE', @article = N'all'
    go
    -- Drop publication
    exec sp_droppublication @publication = N'my_PUBLICATION'
    -- Disable replication db option
    exec sp_replicationdboption @dbname = N'my_PUBLICATION_db', @optname = N'publish', @value = N'false'
    GO
    
    -- Connect Distributor
    :CONNECT [PUBLISHER_SERVER]
    go
    
    exec Distribution.dbo.sp_MSremove_published_jobs @server = 'PUBLISHER_SERVER', 
    @database = N'my_PUBLICATION'
    go
    
    --===========================================================================================
    --THAT DOES NOT GENERALLY GET RID OF THE JOBS FOR YOU
    -- so you need to find them using these selects, and get rid of them manually yourself:
    
    --select * from Distribution.dbo.MSpublications
    --select * from Distribution.dbo.MSpublications
    --===========================================================================================
    
    
    select * from Distribution.[dbo].[MSlogreader_agents]
    where publisher_db = N'my_PUBLICATION'
    
    --found 1 job:
    --PUBLISHER_SERVER-my_PUBLICATION-11
    
    --script the job
    --script the job delete script - and run that - keeping the job creation script just in case
    exec msdb.dbo.sp_help_job @job_id=0x93C63D34E357704B818312B93FCA02FB
    exec msdb.dbo.sp_delete_job @job_id=0x93C63D34E357704B818312B93FCA02FB
    
    
    
    select * from Distribution.[dbo].[MSdistribution_agents]
    where publisher_db = N'my_PUBLICATION'
    
    --here found 2 jobs:
    
    --PUBLISHER_SERVER-my_PUBLICATION-my_PUBLICATION--67
    --PUBLISHER_SERVER-my_PUBLICATION-my_PUBLICATION--68
    
    
    --here is the problem - it cannot find the jobs, the jobs are not even there anymore, one of those things
    exec msdb.dbo.sp_delete_job @job_id=0x0F1564BAACD5464C988DE8957C25C411
    exec msdb.dbo.sp_delete_job @job_id=0x6215C40F999CE248A30EE735E2C0E59D
    
    --Msg 14262, Level 16, State 1, Procedure sp_verify_job_identifiers, Line 41 [Batch Start Line 52]
    --The specified @job_id ('BA64150F-D5AC-4C46-988D-E8957C25C411') does not exist.
    
    
    --Msg 14262, Level 16, State 1, Procedure sp_verify_job_identifiers, Line 41 [Batch Start Line 53]
    --The specified @job_id ('0FC41562-9C99-48E2-A30E-E735E2C0E59D') does not exist.
    
    exec msdb.dbo.sp_delete_job @job_name='PUBLISHER_SERVER-my_PUBLICATION-my_PUBLICATION'
    PUBLISHER_SERVER-my_PUBLICATION-my_PUBLICATION--68




at this point re-create the publication as you would normally do

then put the snapshot to run

wait for it to finish generating the snapshot

`MAYBE YOU DONT NEED TO RUN THE SNAP` - try `without` running it first, most of the time it does work, also you can add just 1-2 `small` articles to the publication so that the snap runs quickly

but if you run the snapshot then you need to wait until it finishes before you can go to the next step - `drop the publication`

[![enter image description here][7]][7]


after that you generate the scripts to `drop that publication` as per the picture below:
[![enter image description here][8]][8]


after that hopefully, when you [run our original scripts above][2], or have a look at the replication monitor
you will not see the faulty publication, only the good ones, in my case just one:

[![enter image description here][9]][9]


  [1]: https://i.sstatic.net/zhhTS.png
  [2]: https://dba.stackexchange.com/q/88923/22336
  [3]: https://i.sstatic.net/tolsk.png
  [4]: https://i.sstatic.net/FrjI9.png
  [5]: https://dba.stackexchange.com/a/135832/22336
  [6]: https://dba.stackexchange.com/a/135770/22336
  [7]: https://i.sstatic.net/mPZhj.png
  [8]: https://i.sstatic.net/IWYMr.png
  [9]: https://i.sstatic.net/DmzWI.png