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

Question

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 ,

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

Answer 1

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.