Executing Process Details

  • Christopher Kutsch

    SSC Eights!

    Points: 893

    Comments posted to this topic are about the item Executing Process Details

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the detailed script.

  • sarmistead

    SSCommitted

    Points: 1671

    Very nice script, thank you for sharing.

    I made a small change in your final SELECT statement to identify the name and step number for any SQL Agent job processes executing; I found this information somewhere online but cannot locate the original source to credit - my apologies. With this change, program_name value for any SQL Agent Jobs resembles "SQLAgent Job: <JobName> - Step <n>"

    SELECT DBName

    , blocking_session_id

    , session_id

    , client_net_address

    , hostname

    , loginame

    , [Duration]

    , wait_type

    , wait_time

    , wait_resource

    , [status]

    , program_name =

    CASE LEFT(program_name, 29)

    WHEN 'SQLAgent - TSQL JobStep (Job '

    THEN 'SQLAgent Job: ' + (SELECT name FROM msdb..sysjobs sj WHERE substring(program_name,32,32)=(substring(sys.fn_varbintohexstr(sj.job_id),3,100))) + ' - ' + SUBSTRING(program_name, 67, len(program_name)-67)

    ELSE program_name

    END

    , command

    , objectid

    , QueryText

    , InputBuffer

    , percent_complete

    , connect_time

    , start_time

    , cpu_time

    FROM @RunTimeInfo

    Steve

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

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