SQL Agent Jobs and sp_who2 active

• Jeet

  SSCommitted

  Points: 1536

  More actions

  October 28, 2005 at 10:11 am

  #110728

  When doing an sp_who2 active the program name description is usually of the form ...

  SQLAgent - TSQL JobStep (Job 0xD0632468C7A4F640BBFE3ADB4D86E644 : Step 4)

  Is there anyway of setting the description to something more recognisable?

  
  Thanks Jeet

• October 31, 2005 at 8:00 am

  This was removed by the editor as SPAM

• thorv-918308

  SSC Veteran

  Points: 229

  More actions

  June 15, 2009 at 2:22 am

  #1009503

  Hi yes

  In SMSS, on menu click tools, customize, click button keyboard.., under Environment, select Keyboard.

  Assign code below to a shortcut e.g. CTRL + 4.

  SELECT p.SPID, Blocked_By = p.Blocked, p.Status, p.LogiName, p.HostName, Program = coalesce('Job: ' + j.name, p.program_name), DBName = db_name(p.dbid), Command = p.cmd, CPUTime = p.cpu, DiskIO = p.physical_io, LastBatch = p.Last_Batch, LastQuery = coalesce((select [text] from sys.dm_exec_sql_text(p.sql_handle)),''), p.WaitTime, p.LastWaitType, LoginTime = p.Login_Time, RunDate = GetDate(), [Server] = serverproperty('machinename'), [Duration(s)] = datediff(second, p.last_batch, getdate()) FROM master..sysprocesses p left outer join msdb.dbo.sysjobs j on substring(p.program_name,32,32) = substring(sys.fn_varbintohexstr(j.job_id),3,100) where p.spid > 50 and p.status 'sleeping' and p.spid @@spid order by p.spid

  Open a new query window, a press CTRL + 4 etc.

  Note: Do not get out of the habit of using sp_who2 active, it is normally the best option for checking processes, however if you need to check job details, run the query above.

• shell_l_d

  Ten Centuries

  Points: 1387

  More actions

  December 6, 2009 at 7:31 pm

  #1088320

  I can't get that script to work on SQL Server 2000 unless I remove the [LastQuery] from select. Any ideas please?

  Get this error if keep [LastQuery] in place:

  -- Server: Msg 170, Level 15, State1, Line12

  -- Line 12: Incorrect syntax near '.'.

  SELECT p.SPID,

  Blocked_By = p.Blocked,

  p.Status,

  p.LogiName,

  p.HostName,

  Program = coalesce('Job: ' + j.name, p.program_name),

  DBName = db_name(p.dbid),

  Command = p.cmd,

  CPUTime = p.cpu,

  DiskIO = p.physical_io,

  LastBatch = p.Last_Batch,

  --LastQuery = coalesce( (select [text] from sys.dm_exec_sql_text(p.sql_handle)), '' ), -- SQL Server 2005+

  --LastQuery = coalesce( (select * from ::fn_get_sql(p.sql_handle)), '' ), -- SQL Server 2000 ? FAILS

  p.WaitTime,

  p.LastWaitType,

  LoginTime = p.Login_Time,

  RunDate = GetDate(),

  [Server] = serverproperty('machinename'),

  [Duration(s)] = datediff(second, p.last_batch, getdate())

  FROM master.dbo.sysprocesses p

  left outer join msdb.dbo.sysjobs j on substring(p.program_name,32,32) = substring(master.dbo.fn_varbintohexstr(j.job_id),3,100)

  where p.spid > 50

  and p.status <> 'sleeping'

  and p.spid <> @@spid

  order by p.spid

• Bru Medishetty

  SSC-Insane

  Points: 23606

  More actions

  December 6, 2009 at 7:50 pm

  #1088323

  What is that you are looking?

  Are you looking for current running Job Names in Activity Monitor?

  ---

  Bru Medishetty

  Blog -- LearnSQLWithBru

  Join on Facebook Page Facebook.comLearnSQLWithBru

  Twitter -- BruMedishetty

• shell_l_d

  Ten Centuries

  Points: 1387

  More actions

  December 22, 2009 at 1:00 am

  #1095268

  Just trying to get this (job monitoring) script to work for SQL Server 2000.

  This works except for the 'LastQuery'.

  Is there a way to get the ::fn_get_sql (SQL Server 2000) to work in the query as is (in one go).

  -- NOTE: dts_name will ONLY return a value 'IF' DTSRUN used with dtsname.

  SELECT

  p.SPID,

  Blocked_By = p.Blocked,

  p.Status,

  p.LogiName,

  p.HostName,

  p.open_tran,

  Program = coalesce('Job: ' + j.[name], p.program_name),

  p.program_name,

  job_name = coalesce(j.[name], ''),

  jobstep_id = coalesce(js.[step_id], ''),

  jobstep_name = coalesce(js.[step_name], ''),

  js.[command],

  dts_name = coalesce(d.[name], ''),

  DBName = db_name(p.dbid),

  Command = p.cmd,

  CPUTime = p.cpu,

  DiskIO = p.physical_io,

  LastBatch = p.Last_Batch,

  -- LastQuery = coalesce( (select [text] from sys.dm_exec_sql_text(p.sql_handle)), '' ), -- SQL Server 2005+

  -- LastQuery = coalesce( (select * from ::fn_get_sql(p.sql_handle)), '' ), -- SQL Server 2000 ? FAILS

  p.WaitTime,

  p.LastWaitType,

  LoginTime = p.Login_Time,

  RunDate = GetDate(),

  [Server] = serverproperty('machinename'),

  [Duration] = datediff(second, p.last_batch, getdate())

  FROM master.dbo.sysprocesses p

  left outer join msdb.dbo.sysjobs j on master.dbo.fn_varbintohexstr(j.job_id) = substring(p.program_name,30,34)

  left outer join msdb.dbo.sysjobsteps js on j.job_id = js.job_id and js.step_id = SUBSTRING( p.program_name, 72, LEN(p.program_name)-72 )

  left outer join msdb.dbo.sysdtspackages d on js.command like ('%dtsrun%'+cast(d.[name] as varchar(100))+'%')

  where

  p.spid > 50

  -- and p.status <> 'sleeping'

  and p.spid <> @@spid

  order by

  p.spid