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!
luyanAsked:
Who is Participating?
 
ptjcbConnect With a Mentor Commented:
I know - that is why I added the link for aba_lockinfo http://www.sommarskog.se/sqlutil/aba_lockinfo
0
 
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
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

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

How about to get all statements of all spid in system?
Thanks!
0
 
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
 
luyanAuthor Commented:
Good stuff! Thanks!
0
All Courses

From novice to tech pro — start learning today.