SQL Server Managed Instance the database got deleted from the Azure portal but it’s still showing up in the SSMS (SQL Server Managed Instance)

One of my customer’s SQL Managed Instance (MI), has an automation where he was deleting database every night and then restoring it.

The automaton was running fine and it was serving the purpose, but one day after running the drop command for couple of DBs, the databases were dropped from the Azure portal, however, the databases were still visible in SSMS (SQL Server Managed Instance) hence the customer was not able to perform the restore operation.

Secondly, if he tried to execute the drop database command in the query editor, it was giving an error the database does not exists.

It was wearied and one of the rare case I believe. While investigating an issue, we found whenever the dropped database happens in SQL MI, it internally performs the Tail Log Backup of that database. It was doing the same in this case, but also in this case, the dropped operation of the database is kind of stuck and was not moving further because the Tail LogBackup InProgress operation was blocking DB drop requests.

The Microsoft SQL Managed Instance product, Engg. Cleared the blocking operation and they helped to drop the database post, then the customer was able to restore the database.

Conclusion:

To avoid such issue, as a best practice, we should implement a wait time period between dropping the database. I believe we should drop one database at a time and also give a wait time between the drop and restore operations.

Happy Learning 😊