SQL Server 2012 SP2 CU4 breaks Data Collection

Last week, one of my clients upgraded their SQL Servers to SQL Server 2012 SP2 CU4.
After the upgrade, the internal DBA notified me that the data collection was not showing any Query Statistics anymore in the Management Data Warehouse report.
I verified the logs of the Query Statistics and saw that the Query Statistics Collection ran for a few minutes and then failed with the following errors:

The external columns for ODS – Get snapshot of dm_exec_requests are out of synchronization with the data source columns. The external column “command” needs to be updated.

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.

The collection never occurred correctly, so the upload to the MDW never happened. That’s the reason why the report was empty.
But why does the collection package starts to fail? Was the DMV dm_exec_requests updated so that the command column size was 32 characters instead of 16? I double checked with BOL and with an older version of SQL Server but the command field always had a length of 32.

Gianluca Sartori (blog | twitter) suggested on Twitter #sqlhelp to modify the SSIS packages to get this fixed. I downloaded the package from the server, modified the command field length to 32 characters and uploaded it again to the server. The QueryActivityCollect package was now running OK but the QuaryActivityUpload package now started to fail with same error on 3 different components (can’t remember which ones). I modified them too and finally I got everything working. … Well… not… Still no data on my report. I’ve noticed that the upload SSIS package was not loading the queries in the [snapshot].[query_stats] table anymore…

I was getting frustrated and stopped with modifying the packages. There must be a better way to fix this! So I came up with the following workaround.

  • I uninstalled the CU4. Yes, you can install SP’s and CU’s. Just go to Programs and Features in the control panel, select the option “View installed updates”, right click on the CU and select uninstall

     

  • Once the CU was uninstalled, my data collection started to work again. I download again the packages from the server and compared them with the CU4 package version.

    And guess what, the packages are changed!

    For example, when I compared the component ODS – Get snapshot of dm_exec_requests

    I get the following in the CU4 version

    While in the CU3 version you get the following:

    So there has definitely be “some” modifications. I’ve also verified the upload package and it’s the same issue.

     

  • I reinstalled CU4 again
  • Stopped the Query Statistics collection

  • I upload the CU3 SSIS package to the server with dtutil, removed all the cache files from the cache folder and restarted the Query Statistics Collection

Finally, my data collection started to work again…
I’ve opened a connect for this. Please vote and let’s hope Microsoft will fix this in the next CU J

Happy collecting!

Pieter

Speaking at SQL Saturday Exeter #372

2 times in a row! Yes, I’m selected again to speak on a very fine SQL Saturday in Exeter!
I had a great time last year and I’m really looking forward to go to the UK again. I’m going by car so if somebody needs a lift, just let me know.
The event will be held on 24th and 25th April 2015 at the Jurys Inn Hotel Exeter, like last year. Really nice hotel, believe me.

This year I’m going to talk about my DBA morning checklist.  In my job as a SQL Consultant, I have my weekly recurring customers. They don’t have a DBA, so I have to perform the daily DBA checklist on a weekly basis 😉 Initially, I’ve started with executing some T-SQL scripts to verify the SQL Servers. But as more servers were installed, my DBA checklist became really time consuming and I didn’t even have the time for a coffee… In this session you will learn how you can perform your DBA morning checklist while sipping coffee. I will demonstrate how you can use Policy Based Management to evaluate your servers and how I configured my setup. By the end of this session, I guarantee you will have more time for your morning coffee!

I have the first timeslot, from 08:00 AM till 08:50, so coffee will be necessary 😉
There are of course a lot of other interesting sessions that day.
On Friday, there will be a lot of full day trainings by William Durkin, David Peter Hansen, Steph Locke, Bob Duffy,  Tobiasz Koprowski, John Martin, André Kamman and Richard Douglas. More information and registration can be found here.

And looks like the pirates are coming this year  :D!

Hope to see you all there in April!

Pieter