Solved

Management Data Warehouse - Data Collector -- dxexec errors

Posted on 2014-04-28
12
796 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
  • 6
  • 2
  • 2
  • +1
12 Comments
 
LVL 22

Assisted Solution

by:plusone3055
plusone3055 earned 100 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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 142

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 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 400 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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

786 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