December 28, 2012

Check the last statement executed by a db process on Microsoft SQL


Following query can be used to check the last statement executed by a particular db process.

Select
'SQL session ID'=spid,
'PID number'=hostprocess,
'Command'=cmd,
'User'=nt_username,
'CPU Time'=cpu/3600.0,
'Number of open transactions'=open_tran,
'Status'=status,
'Statement'=s.text
from sys.sysprocesses p
Cross apply sys.dm_exec_sql_text (p.sql_handle) s
where spid = 107