One probably seldom thinks of the SQL Agent jobs scheduled on the SQL Server instance – unless they fail. What if the job failed because something was changed in the job? Maybe you knew about the change, maybe you didn’t.

Once upon a time, I was in the position of trying to figure out why a job failed. After a bunch of digging and troubleshooting, it was discovered that the job had changed but nobody knew when or why. Because of that, I was asked to provide a low cost audit solution to try and at least provide answers to the when and who of the change.

Tracking who made a change to an agent job should be a task added to each database professionals checklist / toolbox. Being caught off guard from a change to a system under your purview isn’t necessarily a fun conversation – nor is it pleasant to be the one to find that somebody changed your jobs without notice – two weeks after the fact! Usually, that means that there is little to no information about the change and you find yourself getting frustrated.

To the Rescue

When trying to come up with a low to no-cost solution to provide an audit, Extended Events (XE) is quite often very handy. XE is not the answer to everything, but it does come in handy very often. This is one of those cases where an out of the box solution from XE is pretty handy. Let’s take a look at how a session might be constructed to help track agent job changes.

USE master;
GO
-- Create the Event Session
IF EXISTS ( SELECT *
FROM sys.server_event_sessions
WHERE name = 'AuditAgentJobs' )
DROP EVENT SESSION AuditAgentJobs 
    ON SERVER;
GO
EXECUTE xp_create_subdir 'C:\Database\XE';
GO
CREATE EVENT SESSION [AuditAgentJobs] ON SERVER
ADD EVENT sqlserver.sp_statement_completed ( SET collect_object_name = ( 1 )
ACTION ( package0.callstack, sqlserver.client_app_name,
sqlserver.database_name, sqlserver.nt_username, sqlserver.query_hash,
sqlserver.query_plan_hash, sqlserver.session_id, sqlserver.sql_text,
sqlserver.tsql_stack,package0.event_sequence )
WHERE (  [sqlserver].[client_app_name] LIKE 'SQLAgent%'
AND database_name = 'msdb'
/* if desire just agent jobsteps try something like 'SQLAgent%JobStep%' */) ),
ADD EVENT sqlserver.degree_of_parallelism ( 
ACTION ( package0.callstack, sqlserver.client_app_name,
sqlserver.database_name, sqlserver.nt_username, sqlserver.query_hash,
sqlserver.query_plan_hash, sqlserver.session_id, sqlserver.sql_text,
sqlserver.tsql_stack,package0.event_sequence )
WHERE (  database_name = 'msdb'
and statement_type <> 'Select'
/* if desire just agent jobsteps try something like 'SQLAgent%JobStep%' */) ),
ADD EVENT sqlserver.sql_statement_completed (
ACTION ( package0.callstack, sqlserver.client_app_name,
sqlserver.database_name, sqlserver.nt_username, sqlserver.query_hash,
sqlserver.query_plan_hash, sqlserver.session_id, sqlserver.sql_text,
sqlserver.tsql_stack,package0.event_sequence )
WHERE (   [sqlserver].[client_app_name] LIKE 'SQLAgent%'
AND database_name = 'msdb'
/* if desire just agent jobsteps try something like 'SQLAgent%JobStep%' */) )
ADD TARGET package0.event_file ( SET filename = N'C:\Database\XE\AuditAgentJobs.xel' )
WITH ( MAX_MEMORY = 512000 KB
, EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS
, MAX_DISPATCH_LATENCY = 5 SECONDS
, MAX_EVENT_SIZE = 0 KB
, MEMORY_PARTITION_MODE = NONE
, TRACK_CAUSALITY = ON
, STARTUP_STATE = OFF );
GO
ALTER EVENT SESSION AuditAgentJobs ON SERVER
STATE = START;
GO

With this session, I am using degree_of_parallelism as a sort of catch-all in the event that queries that cause a change are not trapped by the other two events (sql_statement_completed and sp_statement_completed). With the degree_of_parallelism event, notice I have a filter to exclude all “Select” statement types. This will trim some of the noise and help track the changes faster.

Looking at data captured by this session, I can expect to see results like the following.

And the degree_of_parallelism event will catch data such as this.

In this example, the deletion of a job was captured by the degree_of_parallelism event. In addition to catching all of the various events that fire as Jobs are being changed and accessed, one will also be able to get a closer look at how SQL Agent runs about its routine.

The Wrap

Extended Events can prove helpful for many additional tasks that may not be thought of on an every day basis. With a little more thought, we can often find a cool solution via Extended Events to help us be better data professionals. In this article, we see one example of that put to use by using XE to audit Agent Job changes.

For more uses of Extended Events, I recommend my series of articles designed to help you learn XE little by little.

Interested in seeing the power of XE over Profiler? Check this one out!

For another interesting article about SQL Agent, check this one out!