System disk run out of space when running heavy SQL queries on SQL Server 2012
SSMS query results cache to the C: drive by default. Go to Tool \ Options. See attached. Change this to another volume with more storage and you should be fine.
Okay, I figured it out: Eric and I were both right!
- The path in the dialog is as I said, just a default path for saving query results.
- Query results are cached to disk (I was wrong), but in the local profile temp folder (
C:\Users\<UserName>\AppData\Local\Tempin my case here). I checked, and there doesn't appear to be an obvious way to turn this caching off.
So the takeaways are:
- Avoid running SSMS directly on the SQL box
- Don't
SELECT *from a huge table in SSMS unless the result set can fit in the profile folder - Make sure the SQL Server max memory setting is configured correctly (it may or may not have contributed to this problem with respect to page file growth)
I have just suffered the same issue. After reading the answers above, I found the following.
Tools | Options is not the answer. Mine was set to Y: drive yet I watched as my query ran and space on the C: drive dived from 2.9GB to 5.04MB (before I killed the query).
So I thought it's probably caching results (as they are very large with every row returned containing a big chunk of XML) to the Temp directory which is what Jon said however he wasn't sure how you'd change that.
What I did to change where temp files are written was to open my Environment Variables and edit User variables TEMP and TMP (which both were set to C:\Temp) to write to Z:\Temp.
I can confirm that after this change, I watched the query create a very large file in my Z:\Temp directory.