Have scheduled drop snapshot replication using SQL Agent. The job is working fine and the publication is getting dropped. But the Job status shows as failed with error:

Krishna Reddy M 0 Reputation points
2023-01-17T14:45:16.9733333+00:00

Error Msg:

Executed as user: abc. Cannot drop the publication because at least one subscription exists for this publication. Drop all subscriptions to the publication before attempting to drop the publication. If the problem persists, replication metadata might be incorrect; consult Books Online for troubleshooting information. [SQLSTATE 42000] (Error 14005). The step failed.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,447 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. LiHongMSFT-4306 30,591 Reputation points
    2023-01-18T05:52:26.1733333+00:00

    Hi @Krishna Reddy M

    There might be some orphaned replication objects, or some other commands blocked the previous command.

    Try this command: EXEC sp_removedbreplication 'PubDB'

    Here the documentation of sp_removedbreplication.

    This stored procedure removes all replication objects on the publication database on the Publisher instance of SQL Server or on the subscription database on the Subscriber instance of SQL Server. Execute in the appropriate database, or if the execution is in the context of another database on the same instance, specify the database where the replication objects should be removed.

    Best regards,

    Cosmog Hong


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.