Blocking sessions in Azure SQL

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