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.
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!