I have an extensive Azure SQL stored proc, called periodically from Azure Data Factory, which is querying another Azure SQL database in the same region using sp_execute_remote. 99.5% of the time this works fine but, on some occasions, the call to sp_execute_remote never returns.
Access to the external resource is via a database scoped credential using a native SQL login granting read only permissions. The call is of the form
insert into ##newrecs exec [sp_execute_remote] @external_server, @stmt, <params>
I have asked the DBA to take a look at what is running on the external database when the hang occurs, but he says he can't see anything amiss. Nothing we are aware of coincides with the failure.
The calling process shows Status RUNNABLE and command EXECUTE. Looking at sys.dm_tran_locks shows that my process has locks on the stored credentials and the temp table for the results. Nothing else has locks on the same resources.
If I capture the @stmt and fire off the same remote query "manually", it returns just fine.
Has anyone any thoughts on
- Why this might be occurring
- How to detect/recover the situation from the calling end
- How to delve deeper into diagnosis on the "remote" Azure database (which is the production environment of a finance system, so I can't mess around too much with it).
This was actually a bug in Azure SQL. After support escalation, Microsoft fixed this around Dec 2021.