Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

    Advertisements
  • Blog Stats

    • 1,584,220 hits
  • Select GETDATE()

    July 2011
    M T W T F S S
    « Jun   Sep »
     123
    45678910
    11121314151617
    18192021222324
    25262728293031
  • Advertisements

Archive for July 18th, 2011

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

Posted in Uncategorized | 9 Comments »