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, 2012 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, 2012 at 16:32

4 Answers 4


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, 2012 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. Aug 2, 2012 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, 2012 at 16:55

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)

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? Mar 14, 2017 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, 2017 at 18:47

Really old question, but I was having the same or similar issue. Running a particular Stored Procedure would see GBs eaten on my C: drive until it filled completely.

In my case the growing files were the ones starting with tempdb located by default in:

C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA

You can check this for your own environment by running:

Use master

name AS [LogicalName]
,physical_name AS [Location]
,state_desc AS [Status]
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb');

You can then relocate it to a different drive/folder:

USE master;

MODIFY FILE (NAME = tempdev, FILENAME = 'E:\tempdb.mdf');

MODIFY FILE (NAME = templog, FILENAME = 'E:\templog.ldf');

Full explanation is here https://www.mytechmantra.com/learnsqlserver/how-to-move-tempdb-to-new-drive-in-sql-server/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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