-- If you are not seeing random failures in following data collection job 
-- collection_set_1_noncached_collect_and_upload , please ignore this blog entry
--
-- This script is to help our dev team understand the root cause of failed data collector job 
-- related thread: https://connect.microsoft.com/SQLServer/feedback/details/644099/collection-job-collection-set-1-noncached-collect-and-upload-failing-sometimes
--
-- Note to customers: Please run this on your test / QA system &
-- send us the logs collected in c:\TraceLogs to sethu.srinivasan@microsoft.com
-- your help would assist us narrow down the root cause of this issue 

-- What this script does? 
-- Audits changes done to syscollector_execution_log_internal directly / through stored procs 
-- Also captures XEvents when validation error 14262 occurs 

-- Read through steps 0 to 5, review one step at a time and execute the T-SQL batch

 
-- Step #0 – On your machine, Create a folder c:\TraceLogs 
-- this is the folder where audit and XE logs will be collected

USE [master] 
GO

-- 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'
 ) 

-- enable server audit
ALTER SERVER AUDIT [DataCollectorObjectAccess_Audit]
 
WITH (STATE = ON) 
GO 

-- create database audit in msdb
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 Session defenition
-- Note: If you are using sql11, replace error = 14262 as error_number = 14262 
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(
 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
use master
GO
 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