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

Get running SQL statements

We can know all active spid by
      select spid from master..sysprocesses

How to know the sql statements running by all these active spid?
Thanks!
0
luyan
Asked:
luyan
  • 4
  • 3
  • 2
  • +1
1 Solution
 
ibostCommented:
I think you'd use the SQL Profiler tool
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
for each spid, you can run
dbcc inputbuffer (spid)

0
 
luyanAuthor Commented:
For one spid using
dbcc inputbuffer (spid)

How about to get all statements of all spid in system?
Thanks!
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I repeat my sentance:
for EACH spid, you can run

there is nothing "for each" built-in in sql server 2000.
now, you might be using sql server 2005?
0
 
ptjcbCommented:
There is also fn_get_sql (if you are running 2000 and sp3 or greater).

From BOL:
DECLARE @Handle varbinary(64);
SELECT @Handle = sql_handle
FROM sys.dm_exec_requests
WHERE session_id = 52 and request_id = 0;
SELECT * FROM ::fn_get_sql(@Handle);
GO


You would create a cursor to walk through the spids and find the active and then the fn_get_sql or inputbuffer. This link has an example of how it is done: http://www.sommarskog.se/sqlutil/aba_lockinfo.html

0
 
luyanAuthor Commented:
ptjcb,
I run following script in SQL Analyzer and get error -- Invalid object name 'sys.dm_exec_requests'.
What is dm_exec_requests?  Thanks!

DECLARE @Handle varbinary(64);
SELECT @Handle = sql_handle
FROM sys.dm_exec_requests
WHERE session_id = 52 and request_id = 0;
SELECT * FROM ::fn_get_sql(@Handle);
GO

Server: Msg 208, Level 16, State 1, Line 2
Invalid object name 'sys.dm_exec_requests'.
0
 
ptjcbCommented:
Oh...that's the 2005 version. This is the 2000 version (from http://support.microsoft.com/kb/325607)

DECLARE @Handle binary(20)
SELECT @Handle = sql_handle FROM sysprocesses WHERE spid = 52
SELECT * FROM ::fn_get_sql(@Handle)


sys.dm_exec_requests is a sql 2005 dynamic view.
0
 
luyanAuthor Commented:
I would like to get all current running statements not only spid=52. Thanks!
0
 
ptjcbCommented:
I know - that is why I added the link for aba_lockinfo http://www.sommarskog.se/sqlutil/aba_lockinfo
0
 
luyanAuthor Commented:
Good stuff! Thanks!
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.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

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