Select Query Eating the Disk Space !!!

Confusing ??? I had the same confusion till I understood exactly what was happening behind the scenes.

While I was working on a production server, as per the customer request, I was running a simple Select * from a Table having some 3-4 million rows The most interesting part is that after some time, I got a call from Windows(WinTEL/Intel) team mentioning that C:\ drive free space have come down drastically well below the standard threshold.

While checking for the same, I saw that it is true, C:\ drive free space is coming down. I simply started searching for the file in C:\ drive which is growing, but was unable to find any. I really got confused as I was thinking, my Select Query can cause problem only if my TEMPDB files reside in C:\, but it was on another drive.


I started working on this to find the culprit. I reprocated the scenario on my environment. I had ran a  select statement on a big table and ran the process monitor to filter the SSMS processes and find out the file name and location.

So this is the culprit which was eating the Drive space.

Solutions
============

1) Whenever you want to run some select statements to retrieve some HUGE results, make sure you direct your output to a File, that resides in a Drive that have enough space. You can also use Ctrl+Shift+F after selecting the Query to activate this option or you can simply click on the below highlighted option before running the query.

                        
                                                      OR

2) Change the default path of TEMP/TMP file to a Different drive.

So from a DBA point of view its always best to go with the first method, as the second option involves a lot of other risks and approvals when a production environment is considered.


Hope this helps out someone to avoid Unnecessary Disk Space Issues...



Thanks
Jinu Varghese