Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Extended events script to monitor a job - SQL Server 2008-2008 R2 Expand / Collapse
Author
Message
Posted Tuesday, October 15, 2013 3:14 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 8:04 AM
Points: 83, Visits: 250
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
Post #1504994
Posted Wednesday, March 19, 2014 12:48 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 26, 2014 11:48 AM
Points: 1, Visits: 92
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
Post #1552768
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse