How to drop a table that was replicated in the past?

Question

An database being used at my job has several tables that appear to be remnants of an previous replication setup. These tables are no longer needed not having been used for at least 10 years so I am trying to delete them. However, it won't let me because it's still indicating they are being used for replication.

The error being encountered is

Cannot drop the table 'dbo.xdc_trnitem' because it is being used for replication. (Microsoft SQL Server, Error: 3724).

This Database does not appear to be setup to be either a Publisher or Subscriber because there is nothing in the Local Publications folder.

I have researched the issue, but so far everything I have found required needs information about replication setup: Articles, Publishers, Subscribers, etc that I don't have. I thought maybe it was a constraint or index on the table causing the issue, but I tried removing those on one of the tables and it still wouldn't let me delete it.

I was wondering if anyone else has encountered this situation and was able to resolve it?

Answer 1

You can run the following script to cleanup any orphan replication objects:

USE [<YourDatabaseName>]
GO

exec sp_cleanupdbreplication
GO
exec sp_removedbreplication
GO

Answer 2

The solution ended up being to run sp_msunmarkreplinfo 'table_name';

Answer 3

Replication stores internal objects in the published database. That you don't see any active publication in SSMS means nothing here. From the error message it's clear those replication objects were not completely removed. See error documentation:

If this error occurs in a database that is not replicated, execute sp_removedbreplication (Transact-SQL) to ensure objects in the database are not marked as replicated.

You need to run sp_removedbreplication on the database to clean it up.