• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1426
  • Last Modified:

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.
0
msdba
Asked:
msdba
  • 2
2 Solutions
 
strickddCommented:
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
 
DonovanRojasCommented:
Just add
s.program_name,

to the query strickdd just sent you
0
 
msdbaAuthor Commented:
Thanks 2both
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now