14

I'm quite new to SQL Server 2012, I would be grateful if someone can help. I have restored a copy of a huge database to SQL Server 2012 and I tried to run some simple queries against it.

I'm trying to run a SELECT query against a database table of 136898115 lines. This SELECT query only has a simple WHERE clause. Every time I run this query, it fails because the system disk (the partition where Windows is installed - C:\) run out of space (this partition has only 6GB free space), and I don't understand why. I defined my tempdb to be on a different drive, which has more than 14 terabytes of free space. Of course that my database is located on a different drive too.

What makes my system partition run out of space? Is it the page file?

  • 2
    im running out of space from the system im running SSMS, but its the same machine. im using SSMS on the actual SQL server. – royv Aug 2 '12 at 16:22
  • 2
    Generally, it's recommended to not run any other applications on a SQL Server (not SSMS) Windows box, or to make sure the max memory setting is low enough to allow enough free RAM. See my answer here: dba.stackexchange.com/a/19776/2718 – Jon Seigel Aug 2 '12 at 16:32
12

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.

enter image description here

  • 1
    If you select Results to File, that's where the Save As dialog box opens up to by default. I don't think SSMS saves result sets to disk by default, but I could be wrong. – Jon Seigel Aug 2 '12 at 17:53
  • 1
    The results you get in your query window are cached per my post. Check your drive, run a large query in SSMS, and check again. You'll see loss of storage, on your C: drive if you haven't specified otherwise. – Eric Higgins Aug 2 '12 at 19:09
  • 1
    thats true. its not related to my page file. i moved my page file to another drive, and still my C: drive ran out of space. – royv Aug 3 '12 at 16:55
11

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\Temp in 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)
7

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.

  • Great information when there is a process you have to go through to get your vm's HD space upgraded and have plenty of network storage. I'm kind of curious though, if you have network issues bottlenecking file transfer rates, might moving outside your c:\ slow query performace? – GibralterTop Mar 14 '17 at 15:59
  • That's 3 years ago and I can't remember exactly what the circumstances were at that time. I now have a laptop with a pathetic amount of RAM and processing power which is another story. I'm pretty sure the Z: drive was just another "local" drive on my VM. Not sure why they didn't label it D: or E: but that part was out of my control. So, for me there wasn't really a network problem. – Nick Ryan Mar 15 '17 at 18:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.