Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

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.

image

And here is the “bad” query. I know its the stupid query but it serves purpose of the blog Smile with tongue out 

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”

image

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.

image 

Opened file Start > Run > %temp%\FileName  (I don’t know why i didn’t see file in folder)

Tmp File

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.

Results to File

OR
Results to File_1

Another workaround would be to change the TEMP/TMP path in environment variables as shown below.
My Computer > Right Click > Properies

Temp Path Change

Hope this would help someone in the world!

Cheers,
Balmukund Lakhani
Twitter @blakhani

Advertisements

9 Responses to “Help : Select query filling up my disk space!!!”

  1. SebaFlu said

    Very helpful – it hits my problem 100% and gives the solution!

    Regard from Berlin/Germany

  2. SDS said

    Good knowledge, especially about changing the temp variables. Thanks!

  3. 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?

  4. 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?

  5. Roxy said

    This post answer the issue I encountered.

    thank you..

  6. This was a a great solution to a problem I was having with my SQL Server.

    Thanks!

  7. Mikkel Brevik said

    This was very helpful. Thanks for including a guide on how to change the temp-directory.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: