I have a table with other tables linked by foreign keys and cascading delete. I can delete rows from the table - except one. It was used in a transaction where the host process crashed. I checked for locks and hanging transactions using
SELECT * FROM sys.dm_exec_requests WHERE DB_NAME(database_id) = 'MyDBName' AND blocking_session_id <> 0
go
SELECT text,* FROM sys.dm_exec_requests CROSS APPLY sys.dm_exec_sql_text(sql_handle)
go
Nothing. Unfortunately, the standard reports in SQL Sever Mangement studio do not work for Azure hosted managed SQL instances.
Any ideas how to identify the cause of the lock/blockage and how to resolve it?