When there is a block in a Microsoft Navision SQL database, in the SQL Activity | Blocking list, the first listed block doesn't show the SQL text (in Playback mode) (4373066)

The Blocking drilldown for the session with blank cell for Session SQL column, also may have “Awaiting Command” in the Command column. This indicates that the type of command being used for that session is not identified.

Analyzing the script to see, when we get “Awaiting Command” in value in the Command column in sys.dm_exec_requests system view, ​in sys.dm_exec_requests system view, if there is a null value in the command column, for any session, those sessions will have “Awaiting Command” value in the Blocking drilldown on Spotlight UI.

Hence the command statement type of that session is not identified by SQL Server system view itself, thus Spotlight is unable to show that information, it is not an issue from Spotlight, but a limitation for SQL Server itself.

You can try to run this below script on that SQL Server using SSMS, while facing this issue to ensure the output directly from the system view:

1. select lb.spid, req.command, req.status, req.blocking_session_id, conn.most_recent_sql_handle
2. from
3. (Select r.session_id As spid
4. From sys.dm_exec_requests As r WITH ( NOLOCK )
5. Where r.blocking_session_id > 0
6. union
7. -- blocking sessions
8. Select r.blocking_session_id As spid
9. From sys.dm_exec_requests As r WITH ( NOLOCK )
10. Where r.blocking_session_id > 0
11. ) lb
12. INNER JOIN sys.dm_exec_sessions sess WITH ( NOLOCK )
13. ON lb.spid=sess.session_id
14. LEFT JOIN sys.dm_exec_requests req WITH ( NOLOCK )
15. ON lb.spid=req.session_id
16. LEFT JOIN sys.dm_exec_connections As conn WITH ( NOLOCK )
17. ON sess.session_id = conn.most_recent_session_id
18. And conn.parent_connection_id Is Null

NOTE: If currently there are no blocked/blocking sessions, user would see 0 rows for this above script

​

Probable reason for this scenario:

In the sys.dm_exec_requests system view, there can be null value in command column for few reasons, one of the reasons for this can be, those sessions are not executing any T-SQL, for example:

• Checkpoint
• Backup

These are not T-SQL instead these are system processes. Microsoft Dynamics NAV Server (the Program name for this session on the screenshot) can create sessions like these which are created by system processes instead of T-SQL, resulting null value in command column in sys.dm_exec_requests view. As these sessions are not T-SQL it is the reason that these sessions won’t have any Session SQL text to show.

​

Conclusion:

It seems this is not a bug but expected behavior only due to the limitation of SQL Server itself.