?
Solved

Management Data Warehouse - Data Collector -- dxexec errors

Posted on 2014-04-28
12
Medium Priority
?
867 Views
Last Modified: 2014-04-30
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.
0
Comment
Question by:dbaSQL
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 2
  • 2
  • +1
12 Comments
 
LVL 22

Assisted Solution

by:plusone3055
plusone3055 earned 400 total points
ID: 40027669
there is a large microsoft support article regarding the cause and resolution of this error.

http://support.microsoft.com/kb/2006769
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 40027700
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
 
LVL 22

Expert Comment

by:plusone3055
ID: 40027722
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 17

Author Comment

by:dbaSQL
ID: 40027729
I hope so.  Thank you for taking the time to look, plusone.  I do appreciate it.
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 40027850
Thank you in advance, Jim.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40029484
I fear that the only way to solve this is to open a call with microsoft on this one...
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 40029651
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
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1600 total points
ID: 40029659
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
 
LVL 17

Author Closing Comment

by:dbaSQL
ID: 40029723
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 40030883
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
 
LVL 17

Author Comment

by:dbaSQL
ID: 40031974
Thank you for the input, Mr. Wills.
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question