Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 11 down vote favorite
2

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?

share|improve this question
Are you running out of space on your CLIENT (i.e. the system you are running SSMS from) or on the SERVER? – JNK♦ Aug 2 '12 at 16:15
Check the page file size and settings. If your SQL max memory setting is left to the default (unlimited), it's possible that Windows swapped out a bunch of stuff to make space. I'm assuming the memory utilization is maxed (please check)? – Jon Seigel Aug 2 '12 at 16:20
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
1  
If SQL Server has allocated up to the max memory amount, and it needs more, it will start kicking out the oldest cached data pages to make space. In an optimal configuration, SQL Server should never hit the page file, because disk is much, much slower than memory. – Jon Seigel Aug 2 '12 at 16:43
show 10 more comments

2 Answers

up vote 8 down vote accepted

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

share|improve this answer
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
up vote 9 down vote

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)
share|improve this answer
The results you see in your app (SSMS) have to be stored somewhere. It doesn't resend the query as you scroll the results, is local. – Eric Higgins Aug 2 '12 at 22:14
@Eric: Yeah, I had figured it was just cached in memory. Thanks: I learned something today. – Jon Seigel Aug 2 '12 at 22:19
I've had it blow up on me before like the OP so that's the only reason I knew :) Seems like I moved it before but must have been moving temp from what you found. – Eric Higgins Aug 2 '12 at 22:43
so what is the solution? i don't understand. if i'll avoid running SSMS on the SQL box, wont it blow out my local machine disk again (the other machine where i do run SSMS)? because SSMS will do the same caching. i'm still a bit confused :( – royv Aug 3 '12 at 17:07
1  
@roy: You're welcome. I'm glad we solved this for you. – Jon Seigel Aug 3 '12 at 17:38
show 6 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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