-- 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
This posting is provided "AS IS" with no warranties, and confers no rights. 
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm