Low Disk Space Alerts in C:\ Drive Caused by .cache Files of MDW

In most environment where SQL Server is installed, there will be some monitoring tool that will be installed, which will monitor the SQL Server for issues like SQL Server being down, databases in suspect state, blocking or suspended sessions, errors in SQL errorlog, old backups for databases, long running jobs and other issues like low disk space, operating system or other related errors in eventlogs, high cpu, high memory issues, etc. Although, in most environments, SQL Server database files are placed on non C:\ drives, but sometimes system databases may be left in C:| drive. There are situations where growth in tempdb can cause C:\ drive free space to go very low or some times MSDB database can also grow very large, but these space issues can be easily identified and fixed. There can also be another situation where page file placed on C:\ drive can grow large and can consume lot of disk space. However, there are times where there will be low disk space issues noticed on C:\ drive due to particular SQL Server files using space from C:\ drive because of using certain features.
Recently, I worked on an interesting issue where C:\ drive was running on disk space and upon checking, found that “C:\Users\SQLServcAccnt\AppData\Local\Temp” folder was taking about 10 GB size and it was few files with .cache extension of 4 GB and growing further and the latest time stamp keep changing to latest time. So, it was evident that there was some process using these .cache files for some purpose. The .cache file names are having the format ServerName_SQLInstanceNameID_{GUID}_#.cache”. Checking the path, it is related to user account which can connect(RDP) to this server or can run services. Based on the name SQLServcAccnt, we found that this was SQL Server service account, which was creating and using these files. Now, it is clear that SQL Server is using these files and not a good idea to delete these files are it may cause some failures with some SQL Server features, so it was important to identify which feature was creating these files and using it. We have run a SQL Server server side trace and later process monitor, which pointed in right direction.
Noticed that these .cache files were created by SQL Server MDW feature. MDW stands for Management Dataware House. There is an option in MDW data collectors to either Cached or non-cached. If cached was chosen, then these .cache files will be created under the users account under which these collectors are running which will be SQL Service account, which is why we are seeing those files because non-cached option was chosen. So, the solution is simple, from SQL Server Management studio, go to Management -> Data Collection -> properties -> Change to non-cached.
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.
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
Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings