Solved

View currently running queries on SQL Server 2005?

Posted on 2007-11-27
4
2,962 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
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.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

726 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