Sethu's blog

— Also captures XEvents when validation error 14262 occurs

USE [master]

GO

– Step #0 – Create a folder c:\TraceLogs

– Step #1 – Create Audit session and start it

— Audit Sessions

– Create a Server Audit to log all audit events to folder

CREATE SERVER AUDIT [DataCollectorObjectAccess_Audit]

TO FILE

( FILEPATH = N’C:\TraceLogs\’

,MAXSIZE = 0 MB

,MAX_ROLLOVER_FILES = 2147483647

,RESERVE_DISK_SPACE = OFF

)

WITH

( QUEUE_DELAY = 1000

,ON_FAILURE = CONTINUE

,AUDIT_GUID = ‘e1f7d882-b26e-4b70-bc03-87af197eb7de’

)

ALTER SERVER AUDIT [DataCollectorObjectAccess_Audit]

WITH (STATE = ON)

GO

USE [msdb]

GO

CREATE DATABASE AUDIT SPECIFICATION [DataCollectorObjectAccess_Audit_MSDB]

FOR SERVER AUDIT [DataCollectorObjectAccess_Audit]

ADD (EXECUTE ON OBJECT::[dbo].[sp_syscollector_event_oncollectionbegin] BY [dbo]),

ADD (EXECUTE ON OBJECT::[dbo].[sp_syscollector_event_oncollectionend] BY [dbo]),

ADD (EXECUTE ON OBJECT::[dbo].[sp_syscollector_event_oncollectionstart] BY [dbo]),

ADD (EXECUTE ON OBJECT::[dbo].[sp_syscollector_event_oncollectionstop] BY [dbo]),

ADD (EXECUTE ON OBJECT::[dbo].[sp_syscollector_event_onerror] BY [dbo]),

ADD (EXECUTE ON OBJECT::[dbo].[sp_syscollector_event_onpackagebegin] BY [dbo]),

ADD (EXECUTE ON OBJECT::[dbo].[sp_syscollector_event_onpackageend] BY [dbo]),

ADD (EXECUTE ON OBJECT::[dbo].[sp_syscollector_event_onpackageupdate] BY [dbo]),

ADD (EXECUTE ON OBJECT::[dbo].[sp_syscollector_purge_collection_logs] BY [dbo]),

ADD (EXECUTE ON OBJECT::[dbo].[sp_sysutility_mi_upload] BY
[dbo]),

ADD (INSERT ON OBJECT::[dbo].[syscollector_execution_log_internal] BY [dbo]),

ADD (UPDATE ON OBJECT::[dbo].[syscollector_execution_log_internal] BY [dbo]),

ADD (DELETE ON OBJECT::[dbo].[syscollector_execution_log_internal] BY [dbo])

WITH (STATE = ON)

GO

– Step #2 – Create XE Session to capture validation error 14262

– XEvent Sessions

CREATE EVENT SESSION Error14262_Session
ON SERVER

ADD EVENT sqlserver.error_reported (

ACTION (sqlserver.tsql_stack, sqlserver.sql_text)

WHERE (error = 14262 ))

ADD TARGET package0.asynchronous_file_target(
– file target

SET filename=‘C:\TraceLogs\14262Errors.xet’,

metadatafile=‘C:\TraceLogs\14262Errors.xem’)

WITH (MAX_MEMORY = 4096KB,

EVENT_RETENTION_MODE =
ALLOW_MULTIPLE_EVENT_LOSS,

MAX_DISPATCH_LATENCY =
10 SECONDS,

MAX_EVENT_SIZE =
0KB,

MEMORY_PARTITION_MODE =
NONE,

TRACK_CAUSALITY =
OFF,

STARTUP_STATE =
OFF

)

GO

– Start event session

ALTER EVENT SESSION Error14262_Session
ON SERVER

state=start

GO

– Step #3 – Enable data collector, run till problem reproes

– Step #4 – turn off XE Session & auditing session

ALTER SERVER AUDIT [DataCollectorObjectAccess_Audit]

WITH (STATE = OFF)

GO

ALTER EVENT SESSION Error14262_Session
ON SERVER

state=stop

GO

– Step #5 – Reading Audit logs and XE Logs

SELECT * FROM sys.fn_get_audit_file (‘C:\TraceLogs\*.*’,default,default);

GO

SELECT CAST(event_data as XML) eventdata

FROM sys.fn_xe_file_target_read_file(‘c:\TraceLogs\*.xet’,

‘c:\TraceLogs\*.xem’,

NULL,

NULL)

GO