Welcome to the world (new face) of Knowledge Sharing Network.
To track the older blog posts refer to our historical knowledge sharing site  and you will find this as your destination for SQL Server knowledge pool.

Follow SQLMaster on Twitter SqlServer-QA.net - Knowledge Sharing Network (@sqlmaster)

June 2011 - Posts - SQL Server Knowledge Sharing Network (@sqlmaster)

June 2011 - Posts

SQL Server Manageability Series: how to change the default path of .cache files of a data collector? #sql #mdw #dba

How to change the default path of .cache files of a data collector after the Management Data Warehouse (MDW has been setup?

This was the question asked by one of the DBAs in a client's place, instantly I enquired that were there any folder specified while setting up the MDW and obvious answer was no as there were left default. This means all the .CACHE files are stored under %C\TEMP directory which may post out of disk space problem on the server where the MDW is setup to collect.

Going back to basics the data collector uses the msdb database extensively for storing configuration information, run-time information, auditing, and collection history information. So it is essential to resize msdb system database with a proper value (atleast 500MB as per my practice) and it must be present on the instance of SQL Server that runs the data collector. In addition to that the relational engine takes care of data collection activities and all the configuration-related tables and views include information about collection sets, collector types, and collection items are given with specific SCHEMA names to identify. The tables and views also contain global data collector parameters, such as the location of the management data warehouse. Database roles for the data collector and management data warehouse are also stored in msdb system database.

As the repository is managed in system database it is essential to know about data collector run-time component which is hosted as a stand-alone process called Dcexec.exe on the server where MDW is setup. It is an important service and component that manages data collection based on the definitions provided in a collection set, and can accept any collection set as input. The data collector run-time component is responsible for loading and executing the SSIS packages that are part of a collection set, you may see the cached and non-chached mode options while setting up the MDW on that instance.

All the collection set options and upload modes are managed by SQL Server Agent service using SSIS packages and the two modes are (as per BOL):

  • Non-cached mode. Data collection and upload are on the same schedule. The packages start, collect and upload data at their configured frequency, and run until they are finished. After the packages finish, they are unloaded from memory.
  • Cached mode. Data collection and upload are on different schedules. The packages collect and cache data until they receive a signal to exit from a loop control-flow task. This ensures that the data flow can be executed repeatedly, which enables continuous data collection.

As referred above the collection mode will use the temporary storage area that is used extensively by the data collection services to store property values, collection sets, calculations and any values that are exposed by collection sets. This is considered as a raw data that is obtained by the collection process and used as aggregated data for the upload methods. During the setup of MDW you will be presented to change the CACHE folder and if no value specified then defualt TEMP directory on the server will be used. 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.

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

  1. Run SQL Server Management Studio tool, and then connect to the instance of SQL Server 2008 R2 in which the error occurs.
  2. Expand the Management folder, right-click Data Collection, and then click Properties.
  3. 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.
  4. 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

Further I would encourage to go through the following chapters from my book titled -Microsoft SQL Server 2008 R2 Administration cookbook: