Extended events script to monitor a job - SQL Server 2008-2008 R2

• pmscorca

  SSCrazy

  Points: 2349

  More actions

  October 15, 2013 at 3:14 pm

  #299195

  Hi,

  I need to create a sql script to use the extended events in order to monitor the execution of

  a SQL Server Agent job.

  I'm searching some examples.

  Any helps, please?

  Thanks

• li_leme

  Grasshopper

  Points: 23

  More actions

  March 19, 2014 at 12:48 pm

  #1698498

  Hi

  DECLARE @JobName NVARCHAR(128) = 'Test';

  DECLARE @Predicate NVARCHAR(MAX)='';

  DECLARE @sqlcmd NVARCHAR(MAX)='';

  SET @sqlcmd ='IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name=''TrackResourceWaits'')

  DROP EVENT SESSION [TrackResourceWaits] ON SERVER;

  CREATE EVENT SESSION [TrackResourceWaits] ON SERVER

  ADD EVENT sqlos.wait_info

  ( WHERE

  (opcode = 1 --End Events Only

  AND duration > 0 -- had to accumulate 100ms of time

  AND ({0})

  ))

  ADD TARGET package0.asynchronous_bucketizer

  ( SET filtering_event_name=''sqlos.wait_info'',

  source_type=0, -- specifies bucketing on column

  source=''wait_type'' -- Bucket by wait_type

  ),

  ADD TARGET package0.ring_buffer(SET max_memory=4096)';

  SELECT @Predicate = @Predicate +

  'sqlserver.client_app_name = ''' +

  'SQLAgent - TSQL JobStep (Job 0x'+

  SUBSTRING(job_id,7,2) +

  SUBSTRING(job_id,5,2) +

  SUBSTRING(job_id,3,2) +

  SUBSTRING(job_id,1,2) +

  SUBSTRING(job_id,12,2) +

  SUBSTRING(job_id,10,2) +

  SUBSTRING(job_id,17,2) +

  SUBSTRING(job_id,15,2) +

  SUBSTRING(job_id,20,4) +

  SUBSTRING(job_id,25,12) +

  ' : Step ' +

  CAST(step_id AS VARCHAR(3)) +

  ')'' OR '

  FROM( SELECT CAST(j.job_id AS VARCHAR(50)) AS job_id,

  js.step_id

  FROM msdb.dbo.sysjobs AS j

  INNER JOIN msdb.dbo.sysjobsteps AS js

  ON j.job_id = js.job_id

  WHERE j.name = @JobName) AS tab;

  -- Append the predicate into the SQL command

  SET @sqlcmd = REPLACE(@sqlcmd, '{0}', LEFT(@Predicate, LEN(@Predicate)-3));

  -- Create the Event Session

  EXEC(@sqlcmd);

  I found this in http://www.sqlskills.com/blogs/jonathan/tracking-extended-events-for-a-sql-agent-job/

  best Regards,

  Liliam