Dropping a SQL Managed Instance database via Azure Data Factory taking hours

Question

I am trying to drop and restore a database via a script activity in Data Factory.

Drop database activity in Data Factory runs forever, hours. Database shows up in SSMS, refreshing, reconnecting, restarting, DB still appears. In the portal for SQL Managed Instance, the Dropped Database is no longer showing.

So neither SSMS nor Data Factory get to find out that the database has been successfully dropped.

Anyone getting a similar issue?

I tried to drop the database, the database gets dropped, but you only know this if you look at the database on SQL MI in the portal. Both SSMS nor Data Factory knows. As such the Data Factory pipeline never moves on to the next activity, which is the restore activity.

Is your connection string using the database that you're trying to drop? — AlwaysLearning, Commented Nov 6, 2024 at 13:59
Hello, no it wasnt, I can see where you are going there though. — PKWilliams, Commented Dec 17, 2024 at 0:19

Pratik Lad · Accepted Answer · 2024-11-07 08:57:17Z

The database gets dropped, but you only know this if you look at the database on SQL MI in the portal. Both SSMS nor Data Factory.

This is a known issue For Azure AQL managed instance whenever the drop database statement is executed in AZURE SQL MI, it internally performs the Tail Log Backup of that database. The database's drop operation had stopped and not progressing forward because the Tail Log Backup InProgress operation was preventing DB drop requests.

To resolve this drop one database at a time and add wait time to it as:

WAITFOR DELAY '00:30';  --it will wait for 30 mis

Drop database activity in Data Factory runs forever, hours.

Switching to single-user mode can assist improve the drop procedure by ensuring there are no other active connections.

ALTER DATABASE [YourDatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

Or check if there is any long running transection in present on Azure SQL MI.

Thanks for the suggestion, sorry 4 l8 reply, as I was dropping databases in lower test environments, there was no need to have those DBs backup up, so I put in a script activity (I was using ADF to refresh the lower environments) to exclude the databases from automatic backup. EXEC msdb.managed_backup.sp_backup_config_basic @database_name = 'TestDB' ,@enable_backup = 0; learn.microsoft.com/en-us/sql/relational-databases/… — PKWilliams, Commented Dec 17, 2024 at 0:18

Collectives™ on Stack Overflow

Dropping a SQL Managed Instance database via Azure Data Factory taking hours

1 Answer 1

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Related