Solved

View currently running queries on SQL Server 2005?

Posted on 2007-11-27
4
2,959 Views
Last Modified: 2012-06-27
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.
0
Comment
Question by:donnatronious
  • 2
4 Comments
 
LVL 3

Accepted Solution

by:
bcaff86 earned 500 total points
ID: 20360652
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
 
LVL 16

Expert Comment

by:SQL_SERVER_DBA
ID: 20360758
SELECT * FROM MASTER..SYSPROCESSES
0
 
LVL 18

Expert Comment

by:Yveau
ID: 20361313
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
 
LVL 18

Expert Comment

by:Yveau
ID: 20361519
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

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
Concerto provides fully managed cloud services and the expertise to provide an easy and reliable route to the cloud. Our best-in-class solutions help you address the toughest IT challenges, find new efficiencies and deliver the best application expe…

932 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

9 Experts available now in Live!

Get 1:1 Help Now