Replication Monitor red crosses are one of those problems that seem simple on the surface but can turn into a frustrating rabbit hole if you don't know where to look. The short answer: red crosses in Replication Monitor almost always mean there are orphaned replication metadata entries left behind after a publication or subscription was deleted incorrectly. There are three main approaches to fix this, and you should try them in order.

Why Do Red Crosses Appear in Replication Monitor?

When you delete a publication or subscription outside of the normal replication teardown process, SQL Server doesn't always clean up the underlying metadata. The distribution database holds dozens of system tables that track publications, subscriptions, agents, and history. If any of those entries survive after the actual replication objects are gone, Replication Monitor will still try to display them and flag them as errors.

This happens more often than you'd expect. A database restore over the top of a publisher, a failed replication removal script, or simply dropping a publication without first removing its subscriptions can all leave this metadata behind. The result is red crosses in Replication Monitor that don't correspond to any active replication setup, and no obvious way to clear them through the GUI.

Method 1: Use sp_removedbreplication

The cleanest starting point is sp_removedbreplication. This system stored procedure removes all replication objects from a specific database, including articles, publications, and subscriber metadata. It's the right first tool for the job.

USE [YourDatabaseName]
GO
EXEC sp_removedbreplication
GO

There's one important condition: this approach only works cleanly if there are no other active publications against that database. If you have multiple publications and only one is causing problems, running sp_removedbreplication will tear down all of them. Check what's active before you run it.

Also note that sp_removedbreplication cleans up objects on the publisher side. It does not remove entries from the distribution database itself. If the red crosses persist after running this procedure, you'll need to go further.

Microsoft's documentation for sp_removedbreplication is the authoritative reference for parameter options and behaviour across different SQL Server versions.

Method 2: Recreate and Properly Remove the Publication

If sp_removedbreplication doesn't resolve the issue, or if it's not appropriate for your situation, there's a reliable workaround. Create a new publication using the exact same name as the orphaned publication that was previously deleted. Once that publication exists again, remove it properly using the standard removal process through SQL Server Management Studio or via T-SQL.

The logic here is straightforward. SQL Server will associate the new publication with the existing orphaned metadata entries in the distribution database. When you then remove the publication correctly, the cleanup process runs properly and removes those entries as intended.

This approach takes a bit more effort but it's often the most reliable path when the distribution database metadata is in an inconsistent state. Make sure the publication name matches exactly, including case, since SQL Server replication is case-sensitive in some configurations.

Method 3: Manually Clean the Distribution Database

When the first two methods don't work, you'll need to go directly into the distribution database and remove the orphaned entries manually. This is the nuclear option, and it requires care.

Before doing anything, close Replication Monitor completely. This is critical. Replication Monitor actively queries these tables and will recreate entries if it's running while you're trying to delete them. Close it, do the work, then reopen it to verify the result.

Start by querying the relevant tables to understand what's there:

SELECT * FROM distribution.dbo.MSpublications
SELECT * FROM distribution.dbo.MSReplication_Monitordata
SELECT * FROM distribution.dbo.MSsubscriptions
SELECT * FROM distribution.dbo.MSdistribution_agents
SELECT * FROM distribution.dbo.MSpublication_access
SELECT * FROM distribution.dbo.MSpublisher_databases
SELECT * FROM distribution.dbo.MSrepl_originators
SELECT * FROM distribution.dbo.MSsnapshot_agents
SELECT * FROM distribution.dbo.MSsnapshot_history
SELECT * FROM distribution.dbo.MSsubscriber_info
SELECT * FROM distribution.dbo.MSsubscriber_schedule
SELECT * FROM distribution.dbo.MSmerge_agents
SELECT * FROM distribution.dbo.MSmerge_articlehistory
SELECT * FROM distribution.dbo.MSmerge_history
SELECT * FROM distribution.dbo.MSmerge_identity_range_allocations
SELECT * FROM distribution.dbo.MSmerge_sessions
SELECT * FROM distribution.dbo.MSmerge_subscriptions

Run each of these SELECT statements and identify which tables contain rows that relate to the orphaned publication or subscription. Look for the publication name, the publisher server name, or the database name to identify the right rows.

Once you've identified the orphaned entries, delete them from the relevant tables. Work through the tables in reverse dependency order to avoid foreign key constraint violations. Generally, delete from history and agent tables first, then subscriptions, then publications, then the publisher database entries.

Do this inside a transaction so you can roll back if something goes wrong:

BEGIN TRANSACTION

-- Example: remove orphaned entries from MSsubscriptions
DELETE FROM distribution.dbo.MSsubscriptions
WHERE publication_id IN (
    SELECT id FROM distribution.dbo.MSpublications
    WHERE publication = 'YourOrphanedPublicationName'
)

-- Continue with other tables as needed

-- COMMIT TRANSACTION  -- Uncomment only when satisfied
-- ROLLBACK TRANSACTION  -- Use this if something looks wrong

Take a backup of the distribution database before you start. Manual deletions from system tables carry real risk, and having a restore point is non-negotiable.

What Order Should You Try These Methods?

Follow this sequence:

  1. Close Replication Monitor before attempting any fix.
  2. Confirm there are no active publications you need to preserve.
  3. Run sp_removedbreplication against the affected database.
  4. Reopen Replication Monitor and check if the red crosses are gone.
  5. If they persist, recreate the publication with the exact original name and then remove it properly.
  6. If that's not practical, query the distribution database tables to identify orphaned entries.
  7. Back up the distribution database.
  8. Delete the orphaned entries manually, working inside a transaction.
  9. Reopen Replication Monitor to confirm the issue is resolved.

What Causes This in the First Place?

Understanding the root cause helps prevent it happening again. The most common scenarios we see in practice are:

  • Restoring a publisher database over itself without first removing replication
  • Dropping a publication database without running the replication removal scripts
  • A failed or incomplete execution of replication setup or teardown scripts
  • Removing a subscriber directly without going through the subscription removal process
  • SQL Server service crashes or unexpected restarts mid-way through a replication configuration change

Getting into the habit of always removing subscriptions before publications, and always using the proper teardown procedures, eliminates most of these problems. If you're decommissioning a replication topology, script the removal process and test it in a non-production environment first.

Key Takeaways

  • Red crosses in Replication Monitor are caused by orphaned metadata in the distribution database, not necessarily by active replication failures.
  • Always try sp_removedbreplication first. It's the cleanest solution but only works safely when no other publications exist on that database.
  • Recreating a publication with the exact original name allows SQL Server to associate and properly clean up the orphaned metadata.
  • If you go into the distribution database manually, close Replication Monitor first. Leaving it open will cause it to recreate the entries you're trying to delete.
  • Always take a distribution database backup before manually deleting from system tables, and wrap your DELETE statements in a transaction.

Replication issues like these are a routine part of managing SQL Server environments, but they can consume significant time if you're not familiar with the internals of the distribution database. At DBA Services, our managed support and SQL Server health checks include a review of replication topology health, orphaned metadata, and distribution database consistency. If you're dealing with persistent Replication Monitor errors or want confidence that your replication setup is clean and well-configured, get in touch with our team.