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