USE [master]
GO
-- Audit
CREATE SERVER AUDIT [jobs]
TO FILE
( FILEPATH = N'PathToSomeFolder'
,MAXSIZE = 0 MB
,MAX_ROLLOVER_FILES = 2147483647
,RESERVE_DISK_SPACE = OFF
)
WITH
( QUEUE_DELAY = 1000
,ON_FAILURE = CONTINUE
,AUDIT_GUID = 'e807469a-6c9d-43f1-af46-cf7b89ba898d'
)
ALTER SERVER AUDIT [jobs] WITH (STATE = ON)
GO
USE [msdb]
GO
CREATE DATABASE AUDIT SPECIFICATION [job_changes]
FOR SERVER AUDIT [jobs]
ADD (UPDATE ON OBJECT::[dbo].[sysjobs] BY [public]),
ADD (UPDATE ON OBJECT::[dbo].[sysjobsteps] BY [public]),
ADD (UPDATE ON OBJECT::[dbo].[sysjobschedules] BY [public])
WITH (STATE = ON)
GO
审计捕获的数据如下所示:
选项 2:扩展事件会话
-- Step 1: extract object_id for the following tables
SELECT object_id
from sys.tables
WHERE name IN ('sysjobs','sysjobsteps','sysjobschedules');
-- Step 2: use those object_ids in the following session:
CREATE EVENT SESSION [capture_job_changes] ON SERVER
ADD EVENT sqlserver.lock_acquired (
SET collect_database_name = (0)
,collect_resource_description = (1)
ACTION(sqlserver.client_app_name, sqlserver.is_system, sqlserver.server_principal_name)
WHERE (
[package0].[equal_boolean]([sqlserver].[is_system], (0)) -- user SPID
AND [package0].[equal_uint64]([resource_type], (5)) -- OBJECT
AND [package0].[equal_uint64]([database_id], (4)) -- msdb
AND (
[object_id] = 1125579048 -- sysjobs
OR [object_id] = 1269579561 -- sysjobsteps
OR [object_id] = 1477580302 -- sysjobschedules
)
AND (
[mode] = (8) -- IX
OR [mode] = (5) -- X
)
)
)
WITH (
MAX_MEMORY = 20480 KB
,EVENT_RETENTION_MODE = ALLOW_MULTIPLE_EVENT_LOSS
,MAX_DISPATCH_LATENCY = 30 SECONDS
,MAX_EVENT_SIZE = 0 KB
,MEMORY_PARTITION_MODE = NONE
,TRACK_CAUSALITY = OFF
,STARTUP_STATE = OFF
);
GO
-- Step 3: add a convenient target to the session (file target?)
为简单起见,我假设您要跟踪 sysjobs、sysjobsteps 和 sysjobschedules。可能还有其他要监视的表。
选项 1:SQL 审计(需要企业版)
审计捕获的数据如下所示:
选项 2:扩展事件会话
XE 会话中的数据如下所示:
关于第二个选项,我写了一篇关于类似主题(跟踪对象使用)的博客文章,其中描述了该技术的细节。基本上,您可以将 IX/X 锁视为对基础表的更新。
此会话仅捕获最低限度,但您可以向其添加更多字段/操作以捕获 sql 文本或计算机名称或任何对您有意义的内容。