View currently running queries on SQL Server 2005?

What are the options/methods available for one who wishes to see the actual queries running and who is running them.  I know how to use sp_who2 if I want to see who is logged in, but I want to see the actual queries.
donnatroniousAsked:
Who is Participating?
 
bcaff86Connect With a Mentor Commented:
Without using 3rd party tools your best bet is to use SQL Profiler and run a trace.  If you are in Mgmt Studio - go to Tools->SQL Profiler.  You can set a ton of options in there but essentially you want to see batches starting and completing.

You can also use dbcc inputbuffer(spid) where spid is the spid from your sp_who command.  It will tell you the last command on the spid.  

Typically I find a trace to be more useful though.
0
 
SQL_SERVER_DBACommented:
SELECT * FROM MASTER..SYSPROCESSES
0
 
YveauCommented:
Run a trace using the SQL Profiler, and you can see the SQL that is being fired against your SQL Server ...

Hope this helps ...
0
 
YveauCommented:
To soon with the points ...
run this query:

SELECT
st.text
FROM
sys.dm_exec_requests r
CROSS APPLY
sys.dm_exec_sql_text(sql_handle) AS st
WHERE r.session_id = <spid number>

Hope this helps ...
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.