Some environments in Dynamics 365 for Finance and Operations are using Azure SQL. In LCS you can see blocked sessions for the environment but you are not able to do anything about them. To solve the issue you need to log into one of the AOSes using RDP and start Management Studio.
The issue in SMSS when sonnecting it to Azure SQL is that you do not have assess to Activity Monitor… you need to solve the issue using T-SQL.
In order to find the blocking session, run this query:
SELECT blocking_session_id AS BlockingSessionID, session_id AS VictimSessionID, (SELECT FROM sys.sysprocesses CROSS APPLY sys.dm_exec_sql_text([sql_handle]) WHERE spid = blocking_session_id) AS BlockingQuery, AS VictimQuery, wait_time/1000 AS WaitDurationSecond, wait_type AS WaitType, percent_complete AS BlockingQueryCompletePercent FROM sys.dm_exec_requests CROSS APPLY sys.dm_exec_sql_text([sql_handle]) WHERE blocking_session_id > 0
To kill the blocking sessions
Kill [SessionID]
That is it…
Note that when you kill a session in SQL the server rolls the transactions back and that might take some time
/Johan
Leave a Reply