Pages

Monday, May 4, 2015

Data Collection (MDW) Errors after Applying SQL 2012 SP2 CU4

Symptoms

After applying CU4, we get the following errors.

Sysssislog messages:

There was an error with ODS - Get snapshot of dm_exec_requests.Outputs[OLE DB Source Output].Columns[command] on ODS - Get snapshot of dm_exec_requests.Outputs[OLE DB Source Output]. The column status returned was: "Text was truncated or one or more characters had no match in the target code page.".

The "ODS - Get snapshot of dm_exec_requests.Outputs[OLE DB Source Output].Columns[command]" failed because truncation occurred, and the truncation row disposition on "ODS - Get snapshot of dm_exec_requests.Outputs[OLE DB Source Output].Columns[command]" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.

Truncation may occur due to retrieving data from database column "command" with a length of 32 to data flow column "command" with a length of 16.

Syscollector_execution_log_internal message:

Failed to create kernel event for collection set: {2DC02BD6-E230-4C05-8516-4E8C0EF21F95}. Inner Error ------------------>
Cannot create a file when that file already exists.

Root cause

CU4 contains this fix:

"An update enables you to exclude system databases from Query Statistics collection sets when you use the management data warehouse in SQL Server"

To implement it, Microsoft added stored procedures, one of them is sp_syscollector_snapshot_dm_exec_requests_internal that returns the column "command" nvarchar(32). However, the SSIS packages are expecting the "command" field to be nvarchar(16).

Workaround

A workaround is to modify this line of code in the stored procedure to return "command" nvarchar(16)
 
CAST(ISNULL (req.command, 'AWAITING COMMAND') AS nvarchar(16)) AS command

Eventually Microsoft needs to modify the "command" length on both the stored procedure and the packages to be nvarchar(32)

Connect item link