While researching my last post I ran across an interesting column I hadn’t noticed before, sys.dm_exec_connections.most_recent_sql_handle. I mentioned it in my previous post but I felt it was interesting enough that I would point it out specifically. Here is the BOL definition:

The SQL handle of the last request executed on this connection. The most_recent_sql_handle column is always in sync with the most_recent_session_id column. Is nullable.

So why is this so interesting? Well primarily for the specific use I mentioned last time. There are requests that are blocked but the session_id doing the blocking doesn’t have an entry in sys.dm_exec_requests. In order to find out what the user has been doing in that session you can at least find out what the last batch they ran was by using most_recent_sql_handle. Remember this is only the last batch not everything in the transaction but it can still be fairly useful since frequently a transaction only has one batch in it.

Filed under: Microsoft SQL Server, SQLServerPedia Syndication, System Functions and Stored Procedures, T-SQL Tagged: code language, DMV, language sql, microsoft sql server, sql statements, system functions, T-SQL