In this article, we’ll explore a peculiar scenario that can arise when managing Always On Availability Groups (AG) in SQL Server. Imagine you have a Multi-node AG cluster, and you’ve recently removed databases from the AG. However, SQL Server still believes these databases are part of the AG, even though they are no longer present. The primary node shows the databases as synced, while the secondary node doesn’t even have them. This discrepancy can be perplexing and may leave you wondering why SQL Server is holding onto these phantom databases.

Possible Reasons for Phantom Databases

To shed light on this situation, let’s consider a few possible reasons:

1. Incomplete Removal Process

It’s possible that the removal of the databases from the AG was not fully completed. If the removal process encountered an error or was interrupted, SQL Server might still consider the databases as part of the AG.

-- Verify the AG configuration
SELECT ag.name AS 'AG Name', 
       db.database_name AS 'Database Name', 
       ags.is_failover_ready AS 'Failover Ready'
FROM sys.availability_groups ag
JOIN sys.availability_databases_cluster db ON ag.group_id = db.group_id
JOIN sys.dm_hadr_availability_group_states ags ON ag.group_id = ags.group_id

2. Orphaned Metadata

In some cases, the metadata associated with the databases may remain in the AG configuration, even after the databases themselves have been removed. This orphaned metadata can cause SQL Server to believe the databases are still part of the AG.

Resolving Phantom Databases

To resolve this issue, you can try the following steps:

1. Remove the Databases from the AG Again

Remove the databases from the AG again, ensuring that the removal process completes successfully.

-- Remove the database from the AG
ALTER AVAILABILITY GROUP MyAG REMOVE DATABASE MyDatabase;

2. Clean Up Orphaned Metadata

Clean up any orphaned metadata related to the removed databases.

-- Clean up orphaned metadata
EXEC sp_clean_db_free_space @dbname = 'MyDatabase';

3. Verify the AG Configuration

Verify that the databases are no longer listed as part of the AG on both the primary and secondary nodes.

-- Verify the AG configuration
SELECT * FROM sys.availability_databases_cluster WHERE group_id = (SELECT group_id FROM sys.availability_groups WHERE name = 'MyAG');

Conclusion

By following these steps and double-checking the AG configuration, you can eliminate any phantom databases and ensure that SQL Server’s understanding of the AG aligns with reality. It’s crucial to maintain a clean and accurate AG configuration to avoid confusion and potential issues down the line.

In conclusion, if you encounter a situation where SQL Server believes databases are part of an AG even after they’ve been removed, don’t panic. By investigating the removal process, cleaning up orphaned metadata, and verifying the AG configuration, you can successfully resolve these phantom databases and keep your AG running smoothly.

Learn more: https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/always-on-availability-groups-sql-server