Starting in SQL Server 2016 we now have the ability to retrieve the Input Buffer in SQL Server Engine using a new DMF(Dynamic
Management Function) called sys.dm_exec_input_buffer. This new DMF has some advantages over DBCC INPUTBUFFER such as
returning a rowset, can be used with sys.dm_exec_sessions or sys.dm_exec_requests by doing CROSS APPLY and it can retrieve
multiple session's input buffer with a simple query.
T-SQL:
This DMF expects 2 input parameters – the session & request id of the specific session. i.e. sys.dm_exec_input_buffer(session_id , request_id)
SELECT
r.session_id, ib.event_info, s.status
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s ON s.session_id = r.session_id
CROSS APPLY sys.dm_exec_input_buffer(r.session_id, r.request_id) ib
WHERE
s.is_user_process = 1
and s.session_id <> @@SPID
and s.status in ('Runnable', 'Running', 'Suspended');
With this new DMF, now we do not have to use the older DBCC INPUTBUFFER command which was not possible to correlate with other Dynamic
Management Views/Functions. Now we can execute sys.dm_exec_input_buffer and return the last executed SQL statement for every user session
for all current executing queries!!!
Permissions:
As per MSDN:
"On SQL Server, if the user has VIEW SERVER STATE permission , the user will see all executing sessions on
the instance of SQL Server; otherwise, the user will see only the current session.
On SQL Database, if the user is the database owner, the user will see all executing sessions on
the SQL Database; otherwise, the user will see only the current session."
Resources:
sys.dm_exec_input_buffer (Transact-SQL)
|
|
|
|
|
|