database deleted in managed instance, but keep say the database exist after syn database from sql server to azure failed

David Zhou 0

I created a distribute alwasy group from premier sql server to azure manged instance , but sync database failed in some point, I deleted the distribute group in local sql server,

however, in the managed insance ,

the group name still exist , I can not delete it,
database didn't exist in managed instance, but when i recreate it, keep saying the database exist.

David Zhou 0 Reputation points

2025-02-25T16:39:48.0833333+00:00

the distributed name still exist on managed instance, can not deleted
Mallaiah Sangi 85 Reputation points Microsoft Vendor

2025-02-25T18:34:46.15+00:00
Hi @ David Zhou

Thanks for the Question and using Microsoft Q&A

As per my understanding, you are not able to deleting the distribute group and sync database from SQL Server Always on Distributed group configuration.

Please follow the below steps to remove a database from a Distributed Availability Group

Turn HADR off on forwarder on secondary AG

From forwarder server at secondary AG run the following command setting your database name

USE master

GO

ALTER DATABASE [db_name] SET HADR OFF;

GO

Once this has ran it breaks the Distributed AG replication for the database

Remove DB from Primary Server AG

On the primary server of the primary AG, remove the database from the AG

Primary server will go back to a normal un-replicated database

Primary replica will go into a restoring state

Remove it from each AG starting on secondary AG replica in the following order;

Replica on secondary AG

Forwarder on secondary AG

Replica on primary AG

Primary on primary AG

The database should now be successfully removed from all locations.

Please find the below mentioned links for more information.

sql server - Cannot remove Database from Distributed AlwaysOn Group on Secondary Windows Cluster - Database Administrators Stack Exchange

I hope this information helps. Please do let us know if you have any further queries.
David Zhou 0 Reputation points

2025-02-25T19:22:21.79+00:00

your scipt definitely not work. it is not on the premier server, it is on the sql manged instance . the primer group already deleted. left orpahn in the zaure manged isntance. no any database in the group.
David Zhou 0 Reputation points

2025-02-25T19:24:35.79+00:00

your solution defientely not work .I already tried this. it is not normal alwasy on group. it is in az manged instance. the group actually had deleted in the sql server node.but still show in manged instance node which prevent to delete it.
Mallaiah Sangi 85 Reputation points Microsoft Vendor

2025-02-25T20:01:04.89+00:00

Hi @ David Zhou

If you are not able to be creating database with same name, this can happen due to some residual metadata or temporary files that haven't been fully cleaned up.

Here are a few steps you can try to resolve this issue:

Check if the database is actually deleted

First, verify if the database is completely removed. You can use SQL Server Management Studio (SSMS) or a query to check if the database still exists:

SELECT name FROM sys.databases WHERE name = 'YourDatabaseName';

If it still shows up, it might not have been deleted properly.

Check for active connections

If the database has active connections, the deletion might not have been successful. You can check for active connections and close them by using:

To see active connections to your database:

SELECT spid, dbid, login_time, last_batch, program_name, status

FROM sys.sysprocesses

WHERE dbid = DB_ID('YourDatabaseName');

Kill the process (if needed):

KILL [spid];

After killing any active connections, try dropping the database again:

DROP DATABASE IF EXISTS YourDatabaseName;

Check for database files:

Sometimes, a database might have been removed from the system but its associated files might still exist on disk. Check the file system for any remaining files related to your database MDF and LDF files. If so, manually delete those files before recreating the database.

Use DROP DATABASE and IF EXISTS clause:

Ensure you're using the correct syntax. The IF EXISTS clause ensures the database is only dropped if it already exists:

DROP DATABASE IF EXISTS YourDatabaseName;

If the system is still showing that the database exists after this, you may need to check if there's a replication or secondary system that is holding onto a reference of the database.

Check database status in the managed instance:

Some managed database instances might have additional features that cache or hold references. For instance, if there are any automated backups or restore points, these might affect the deletion process.

I hope this information helps. Please do let us know if you have any further queries.
Mallaiah Sangi 85 Reputation points Microsoft Vendor

2025-02-26T20:26:39.1466667+00:00

Hi @David Zhou

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Mallaiah Sangi 85 Reputation points Microsoft Vendor

2025-02-27T20:36:45.86+00:00

Hi @David Zhou

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

Share via

database deleted in managed instance, but keep say the database exist after syn database from sql server to azure failed

Your answer