Help : Select query filling up my disk space!!!
Posted by blakhani on July 18, 2011
Sounds strange? Yeah and that’s the reason I am blogging about this. And recently I saw this question in forum as well. Have you ever come across this scenario?
Here is the repro of the issue and explanation. Below is the screenshot of my windows explorer space usage before I fired the query.
And here is the “bad” query. I know its the stupid query but it serves purpose of the blog 
Select * from sys.objects a1, sys.objects a2, sys.objects a3, sys.objects a4, sys.objects a5, sys.objects a6, sys.objects a7, sys.objects a8, sys.objects a9, sys.objects a0
Space would not be taken up as soon as you run the query. Once its start producing “HUGE” result set. I left the query running for around 6 min and here is the “proof”
Empty space in C drive dropped from 21.9 GB to 20.9 GB (Yeah you read is correct, it’s 1 GB) I have run process monitor and filtered results for ssms.exe.
Opened file Start > Run > %temp%\FileName (I don’t know why i didn’t see file in folder)
If you look closely and compare above tmp file with management studio result window, it would be evident that ssms is writing the result in tmp file.
Moral of the Story:
- Don’t run query which has HUGE result set on server directly.
- If you have such need, redirect the output to “file” rather than shown in management studio result window. This can be done by two ways.
OR
Another workaround would be to change the TEMP/TMP path in environment variables as shown below.
My Computer > Right Click > Properies
Hope this would help someone in the world!
Cheers,
Balmukund Lakhani
Twitter @blakhani
Help: SQL Server 
SebaFlu said
Very helpful – it hits my problem 100% and gives the solution!
Regard from Berlin/Germany
SDS said
Good knowledge, especially about changing the temp variables. Thanks!
svesda4 said
Thanks for this post. Very helpful.
Question, I see you have the shortcuts assigned for Results to File/Text, my SSMS 2010, I don’t have these two assigned, is there a way to do so?
svesda4 said
Hi, very useful post, thanks for that.
I see you have shortcuts assigned to Results To File/Text functions, I am using SSMS 2010 and I don’t have these assigned, how do I assign them please?
Roxy said
This post answer the issue I encountered.
thank you..
Ian Pearson said
This was a a great solution to a problem I was having with my SQL Server.
Thanks!
blakhani said
I am glad that it could help you.
Mikkel Brevik said
This was very helpful. Thanks for including a guide on how to change the temp-directory.
blakhani said
Great!