Have you ever wondered what SQL query was last executed by your users across all SQL Server databases on your server? I have seen some solutions on the internet that use the sysprocesses view to retrieve this information. In this post, I will show you how this information can be retrieved better using
Dynamic Management Views.
Please use this query:
SELECT conn.session_id, sson.host_name, sson.login_name,
sqltxt.text, sson.login_time, sson.status
FROM sys.dm_exec_connections conn
INNER JOIN sys.dm_exec_sessions sson
ON conn.session_id = sson.session_id
CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) AS sqltxt
ORDER BY conn.session_id
Here’s a quick overview of what these DMV’s and DMF do
sys.dm_exec_connections - Returns information about the connections established to this instance of SQL Server and the details of each connection
sys.dm_exec_sessions - Returns one row per authenticated session on SQL Server. sys.dm_exec_sessions is a server-scope view that shows information about all active user connections and internal tasks. This information includes client version, client program name, client login time, login user, current session setting, and more
sys.dm_exec_sql_text - Returns the text of the SQL batch that is identified by the specified sql_handle
In the last statement, we are passing the value contained in the most_recent_sql_handle column of this DMV to the sys.dm_exec_sql_text DMF. The DMF returns the text of the sql query, whose sql_handle we passed to it. This sql_handle that we passed, uniquely identifies the query.
Here’s the output
