Management Data Warehouse - Data Collector -- dxexec errors

I have been putting together a MDW for a little while now, in SQL Server v2012, to perform the collection and upload of all sorts of server and database statistics.   I have actually posted a couple inquiries up here already, but I just deleted them, because I never received any responses.  

Last round, I'm going to try one more time.   I would greatly appreciate any EE input.


Short story -  The MDW is on a v2012 instance, I collect data from both v2012 and v2005 instances.  The v2005 instances are targeted (successfully) with custom collectors that I have created on the MDW, which target the v2005 instances via linked server.  The v2012 instances use the three default system collectors, and one custom collector:
 
  Server Activity
  Disk Usage
  Query Statistics
  Index Statistics

I have resolved all of my errors and problems except for one.  

The system collectors for Server Activity and Query Statistics both fail with this error:
dcexec: Error: Internal error at Main (Reason: The system cannot find the file specified).  

Here I am referring to the SQL Server Agent jobs that were created when the data collector was enabled, that is used to 'collect' the data.  It is the two collector Agent jobs that are failing with the above error.

Please let me know if there is any other detail that I can provide, that will help an Expert to help me.
LVL 17
dbaSQLAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
then you want to check the config/location of the cached files:
http://sqlblogcasts.com/blogs/ssqanet/archive/2011/06/15/sql-server-manageability-series-how-to-change-the-default-path-of-cache-files-of-a-data-collector-sql-mdw-dba.aspx

copy of the relevant part
Coming to the subject line here is the process to modify the folder once MDW is up and running:

    Run SQL Server Management Studio tool, and then connect to the instance of SQL Server 2008 R2 in which the error occurs.
    Expand the Management folder, right-click Data Collection, and then click Properties.
    If a directory is specified as a Cache directory directory, the directory that is specified is the location of the Data Collector cache files.

    If a directory is not specified as a Cache directory directory, the default cache directory is the local temporary directory of the account that runs the collection set.
    This account may be the SQL Server Agent service account. For example, the collection set is run by an account that is named “SQLMDW”. In this example, the temporary directory of this account is located in a path that resembles the following:  C:\Users\SQLMDW\AppData\Local\Temp 

Open in new window

0
 
plusone3055Connect With a Mentor Commented:
there is a large microsoft support article regarding the cause and resolution of this error.

http://support.microsoft.com/kb/2006769
0
 
dbaSQLAuthor Commented:
Yes, I saw that, and I affected the change that it suggests for the smaller packet size.  There was no impact or change to the problem.

That paper does not clearly indicate where the problem is --- be it the targeted server, or the MDW.  That network packet size was changed on both.

Do you have any additional information?
0
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 
plusone3055Commented:
I unfortuantely do not :(
Never fear thoough
others will read and respond therr are some high end MVP's in here
I would  Poke Jimhorn
http://www.experts-exchange.com/members/jimhorn.html
0
 
dbaSQLAuthor Commented:
I hope so.  Thank you for taking the time to look, plusone.  I do appreciate it.
0
 
dbaSQLAuthor Commented:
Thank you in advance, Jim.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I fear that the only way to solve this is to open a call with microsoft on this one...
0
 
dbaSQLAuthor Commented:
I just tried something --   I recognized that it was only the cached collectors that were having this problem.  The non-cached have been working fine.  So, I changed the Server Activity and Query Statistics collection sets to non-cached.  

It worked fine.  

Previously, the upload would work without error, but I wasn't writing any data.
The collection would fail every time, with the message I posted above.

All I did was change it to non-cached.  It recreated the Agent job, which I ran to successfully populating all of the snapshot entities.  

Both Server Activity and Query Statistics are now writing data to the MDW successfully.

I'm hesitant to say all is well.  They were defined as cached for a reason.  You guys have any thoughts?
0
 
dbaSQLAuthor Commented:
I am almost certain I saw somewhere in the setup to leave the cached directory blank.  Which I what I did.

Makes sense to place them with the tempdb.

>>Going further it is essential and best practices to store these .CACHE files on a different drive where data or log files are not located, I would suggest to use the same folder where TEMPDB data file is located.


I found my workaround, but I believe you found the real source of the problem, Angelll.  

Thank you both for looking this way.
0
 
Mark WillsTopic AdvisorCommented:
Looks like I am too late to the party... But what I found so far (not having the same problem in 2012, but experienced similar in 2008)...

There is a connect item logged and suggests cache : https://connect.microsoft.com/SQLServer/feedback/details/542515/data-collector-error

Then searching for MDW and cache, came up with : http://sqlblogcasts.com/blogs/ssqanet/archive/2011/06/15/sql-server-manageability-series-how-to-change-the-default-path-of-cache-files-of-a-data-collector-sql-mdw-dba.aspx which invites a lot of thought as to directories and location of cache files.

And, would still manage packet size (another cause for the error) :
exec sp_configure 'network packet size', 16000

Would be interesting to see the full DC logs. This is one of those rotten MS errors that doesn't help too much.

I should check my "Jim" emails in a more timely manner *laughing*
0
 
dbaSQLAuthor Commented:
Thank you for the input, Mr. Wills.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.