ALTER DATABASE ADD FILE fails due to running backup in azure sql Managed instance

Question

We are running a monthly script for partition update. in the script we also run ALTER DATABASE ADD FILE and ALTER DATABASE REMOVE FILE. The script should be for running for about 20 minutes. when running the script we are getting an error:

Backup, file manipulation operations (such as ALTER DATABASE ADD FILE) and encryption changes on a database must be serialized. Reissue the statement after the current backup or file manipulation operation is completed.

This error appear because there is an ongoing backup at the same time . the backup is done automatically by sql azure managed instance.

Since we don’t know the times of the backups we need a solution to be able to run our script without this error

Answer 1

Managed Instance runs log backup every 5 minutes (unless if you run your script while full backup is running) and file/encryption modification statements are not allowed in this period. You could implement some retry logic or explicitly check are there any ongoing backup operations:

SELECT r.session_id,r.command,CONVERT(NUMERIC(6,2),r.percent_complete) 
AS [Percent Complete],CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time], 
CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min], 
CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min], 
CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours], 
Stmt = CONVERT(VARCHAR(1000), (SELECT SUBSTRING(text,r.statement_start_offset/2, 
CASE WHEN r.statement_end_offset = -1 THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset)/2 END) 
FROM sys.dm_exec_sql_text(sql_handle))) 
FROM sys.dm_exec_requests r WHERE command LIKE '%BACKUP%'