Management Data Warehouse (MDW) was introduced starting with SQL Server 2008 and this helps for performance troubleshooting by capturing some data. MDW is a set of components that enable a database developer or administrator to quickly track down problems that could be causing performance degradation. MDW feature consists of The Data Collector, The MDW database and The MDW reports components. Many of the reports included in MDW will provide suggestions for improving performance, based on information gathered by the Data Collector in areas of
Query performance statistics and use of indexes, Resource locking and blocking and Disk usage. Although MDW is a great feature and useful in many cases, but sometimes just link any other feature, it will create some issues, like low disk space issues on C:\ drive when cached option is chosen for data collection, then it will create .cache files under C:\ drive service account temp folder. There are other issues as well like .cache files are deleted or missing or corrupt leads to the MDW failures.
Some of the common errors or known issues with Management Data Warehouse (MDW) are mentioned below.
You may receive errors like below.
– The file had bad version and flags information. The file is damaged or not a SSIS-produced raw data file. component “Raw File Destination” (57) failed the pre-execute phase and returned error code 0xC0202061
– Encountered bad metadata in file header. The file is damaged or not a SSIS-produced raw data file.
component “Raw File Destination” (57) failed the pre-execute phase and returned error code 0xC020205E
– Unexpected end-of-file encountered while reading X bytes from file “Y”. The file ended prematurely because of an invalid file format. component “Raw File Destination” (57) failed the pre-execute phase and returned error code 0xC0202069
– The adapter encountered an unrecognized data type of X. This could be caused by a damaged input file (source) or by an invalid buffer type (destination). component “Raw File Destination” (57) failed the pre-execute phase and returned error code 0xC020206B
– String too long. The adapter read a string that was X bytes long, and expected a string no longer than Y bytes, at offset Z. This could indicate a damaged input file. The file shows a string length that is too large for the buffer column. component “Raw File Destination” (57) failed the pre-execute phase and returned error code 0xC020206C
All the above error occur if one or more Data Collector cache files gets corrupted. The corruption may be caused for different reasons, like Data Collector may have encountered an exception while running or The disk must have run out of free space while Data Collector is writing to a cache file or A firmware or a driver problem. So, to resolve the issues, check the disk space on C:\ drive and ensure there is enough free space, make sure all firmware and third party drivers are up to date, and check MDW data collector properties to make sure they are all correct and importantly understood what they do, like cached and non-cached collector properties where enabling cached property will create .cache files under C:\ drives SQL Service account temp folder.
Hope this was helpful.
This is applicable for below versions of SQL Server
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014
SQL Server 2016
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings