Executing Process Details

• Christopher Kutsch

  SSC Eights!

  Points: 893

  More actions

  July 16, 2015 at 6:00 pm

  #300320

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

• akljfhnlaflkj

  SSC Guru

  Points: 76202

  More actions

  August 18, 2015 at 7:27 am

  #1820714

  Thanks for the detailed script.

• sarmistead

  SSCommitted

  Points: 1671

  More actions

  September 9, 2015 at 10:11 am

  #1825459

  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