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

  • neotokyo

    SSCertifiable

    Points: 7239

    i know i could always check the management studio activity, but

    wondering if there was a way i can see the commands behind this

    maybe with another query?

    _________________________

  • monster

    SSC Enthusiast

    Points: 119

    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: 104200

    or there is also dbcc inputbuffer(spid)

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

  • neotokyo

    SSCertifiable

    Points: 7239

    dbcc inputbuffer(spid) sounds easy to me.

    _________________________

  • monster

    SSC Enthusiast

    Points: 119

    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

    now that is EXACTLY what i was looking for.

    excellent work!

    thanks!

    _________________________

  • hintze

    SSC Journeyman

    Points: 75

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

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

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