Solved

Management Data Warehouse - Data Collector -- dxexec errors

Posted on 2014-04-28
12
772 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
 
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
How to select Week Start and Week End dates 5 27
SQL Server 2012 Row Selection 2 30
Azure SQL DB? 3 21
SQL JOIN + SUBQUERY? 3 14
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

762 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now