September 21, 2009 at 2:07 am
Hi All,
I am running SQL Server 2005 and when i run large queries i find that i am losing significant amounts of hard drive space very quickly (about 7gb) to the point where the query stops running and i have about 100mb left of space. I then have to quit management studio and run a cleanup to get the space back.
Is there a way to stop this happening or is it time to get a bigger hd??
Thanks,
Spin.
September 21, 2009 at 5:54 am
Where is space going?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.September 21, 2009 at 6:49 am
It really depends on what the queries are doing. A select statement without any temp tables, table variables, or ORDER BY clauses, that pulls data through seeks and joins through loop joins shouldn't cause a bit disk space usage. However, a query with lots of merge joins or table valued user defined functions... who knows, will use up tempdb resources at a might pace.
So, what are you queries doing?
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
September 21, 2009 at 6:51 am
george.frewin (9/21/2009)
Hi All,I am running SQL Server 2005 and when i run large queries i find that i am losing significant amounts of hard drive space very quickly (about 7gb) to the point where the query stops running and i have about 100mb left of space. I then have to quit management studio and run a cleanup to get the space back.
Is there a way to stop this happening or is it time to get a bigger hd??
Thanks,
Spin.
All queries, or some? any jobs doing this?
Where is your tempdb located? on the mentioned drive?
chances are your tempdb is set to auto grow without any limits.....
let us know...
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
September 21, 2009 at 7:58 am
It sound like a memory bottleneck.
In this case SQL Server is actively paging, usually on C: drive, eating drive space.
If you see the pagefile.sys file with size about 7Gb on the root of C: drive, you have this issue.
September 21, 2009 at 1:42 pm
SSMS caches the data from your queries locally in TEMP. If you are returning a large amount of data (which is sounds like you are doing), then yes - you are going to run out of space.
You can either get a larger drive, or move TEMP to a much larger drive (if available).
Ideally, you wouldn't be running queries that return that much data - because there really is no way you can actually do anything with 7GB of data in SSMS.
The last option, if all you need to do is run the queries until completed is to modify your settings in SSMS to discard the results. That might help...
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply