How to do DBCC InputBuffer on all active SPID at once in SQL Server

Hi All,
I use SP_who2 'active' to find the current activity on sql server instanse.

but when I try to identify particular SPID using DBCC Inputbuffer, by the time I run it the process will be gone.

Is there a way to identify all the active  SPID's  and do the DBCC InputBuffer on all active process at same time so that the DBCC result for the SPID will be the same as in SP_who2?

Thanks in advance.
msdbaAsked:
Who is Participating?
 
strickddConnect With a Mentor Commented:
Try this:

select
s.session_id,
s.login_name,
s.status session_status,
s.cpu_time session_cpu,
s.logical_reads session_logical_reads,
s.reads session_reads,
s.writes session_writes,
sql_details.objectid session_current_procedure,
object_name(sql_details.objectid) session_current_procedure_name,
sql_details.text session_current_sql_text
from sys.dm_exec_sessions s
join sys.dm_exec_requests req
on s.session_id = req.session_id
cross apply sys.dm_exec_sql_text(req.sql_handle) sql_details
where s.session_id <> @@spid

Open in new window

0
 
msdbaAuthor Commented:
Thank you so much, that is really wat I was looking for.
Is there a way to find  the Applicaiton or Program name that is running the particularspid  using the above sql statement?
0
 
DonovanRojasConnect With a Mentor Commented:
Just add
s.program_name,

to the query strickdd just sent you
0
 
msdbaAuthor Commented:
Thanks 2both
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.