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

Posted on 2011-10-18
Last Modified: 2012-06-27
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.
Question by:msdba
    LVL 28

    Accepted Solution

    Try this:

    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


    Author Comment

    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?
    LVL 1

    Assisted Solution

    Just add

    to the query strickdd just sent you

    Author Closing Comment

    Thanks 2both

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
    When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now