Data Collection - Query Statistics Collection Set not working anymore after upgrade to SQL Server 2012 SP2 - by Pieter Vanhove

Status : 

  Fixed<br /><br />
		This item has been fixed in the current or upcoming version of this product.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


16
0
Sign in
to vote
ID 1138283 Comments
Status Closed Workarounds
Type Bug Repros 6
Opened 2/19/2015 5:07:13 AM
Access Restriction Public

Description

Hi,

I've upgraded my instance from SQL2012 SP1 CU10 to SQL 2012 SP2 CU4, I've noticed that the Query Statistics Collection set of the Data Collection is not working anymore.
When I stop and restart the collection set, I get the following errors:


Message
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.".

Message
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.

Message
SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on ODS - Get snapshot of dm_exec_requests returned error code 0xC020902A.  The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.  There may be error messages posted before this with more information about the failure.

I've also removed the complete data collection with sp_syscollector_cleanup_collector and recreated it again but still the same issue.

Any idea how I can fix this?

Sign in to post a comment.
Posted by Sethu [MSFT] on 1/6/2016 at 12:01 PM
If you are using SQL 2012, Can you please install recent CU and check it out?

https://support.microsoft.com/en-us/kb/3098512
Cumulative update package 9 for SQL Server 2012 SP2
SQL12_SP2_QFE_CU9_x64

Thanks
Sethu Srinivasan [MSFT]
Posted by Sethu [MSFT] on 12/3/2015 at 12:54 PM
Hello Pieter,
Can you please check if you still see this issue after upgrading to SQL 2012 SP3?
Link: https://www.microsoft.com/en-us/download/details.aspx?id=49996

Thanks
Sethu
Posted by ChrisAllenNC on 6/15/2015 at 12:11 PM
We've been able to resolve the issue by taking the SSIS packages off of an instance that wasn't updated yet and reloading them after the update. Comparing the XML data of the old/new packages it appears that the package built for 2008/2008 R2 ended up in the 2012 update. In 2008/2008 R2 the command column is only 16 characters. Also if you look at the XML the connection was dropped from SQLCLI11 to SQLCLI10.
Posted by Pieter Vanhove on 2/23/2015 at 7:46 AM
I have noticed that the packages QueryActivityCollect.dtsx and QueryActivityUpload.dtsx have been changed, which is causing th issue.
In SP2, the collection and upload was done by a fixed query but in CU4 i've noticed that the Collect and uploads are changed into the usage of stored procedures. For example: EXEC [msdb].[dbo].[sp_syscollector_snapshot_dm_exec_requests]