sys.processes? how to query the commands as well?

Question

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

monster SSC Enthusiast Points: 119 More actions · Answer 1

In sql 2000, I used

SELECT @SQL = text

FROM ::fn_get_sql(@Handle)

where @Handle is the sql_handle from sysprocesses.

That seems to still work on 2005.

george sibbald SSC Guru Points: 104210 More actions · Answer 2

or there is also dbcc inputbuffer(spid)

---------------------------------------------------------------------

neotokyo SSCertifiable Points: 7239 More actions · Answer 3

dbcc inputbuffer(spid) sounds easy to me.

_________________________

monster SSC Enthusiast Points: 119 More actions · Answer 4

I did a little research myself, as I was looking for something like it as well.

To get the currently running processes, use:

SELECT

session_id,status,

command,sql_handle,database_id

,(SELECT text FROM sys.dm_exec_sql_text(sql_handle)) AS query_text

FROM sys.dm_exec_requests r

WHERE session_id >= 51

For more detail about was has been running, try:

select

s.session_id, s.login_name, s.host_name, s.status,

s.program_name, s.cpu_time, s.last_request_start_time,

(SELECT text FROM sys.dm_exec_sql_text(c.most_recent_sql_handle)) AS query_text

from sys.dm_exec_sessions s, sys.dm_exec_connections c

where s.session_id = c.session_id and

s.session_id > 50

order by s.last_request_start_time desc

neotokyo SSCertifiable Points: 7239 More actions · Answer 5

now that is EXACTLY what i was looking for.

excellent work!

thanks!

_________________________

hintze SSC Journeyman Points: 75 More actions · Answer 6

Query just saved me a bunch of time looking through the DMVs. Thank you!