December 7, 2023 at 9:51 am
Hi,
I keep getting this error message saying that I don't have enough disk space whenever I run a query. based on suggestions online I tried freeing up some disk space by cleaning up temporary files. This however is a temporary fix
I noticed that my C drive quickly goes from, say, 9 Gb of free space down to 0. I checked with my colleagues and they don't seem to be having any such issues. I am not very familiar about how sql server works behind the scenes but could it be that my query results are being saved somewhere on my local drive ?
The database I am working out is not on the local host. It is a server that I connect to with numerous other databases
Any help will be much appreciated
December 7, 2023 at 12:27 pm
This could be so many different things it's going to be hard to give you an answer. First of all, you haven't stated what you're doing. Is this a SELECT query and your drive is filling up? Then we need to look, probably, at what the query is doing and how much it's using tempdb to satisfy the code. Hash joins or hash aggregates can use quite a lot of tempdb. Sort operations. Other aggregates. Calculations. It's really hard to say without a lot of detail.
Are you moving data around, INSERT/UPDATE/DELETE. Well, how much. Is it doing sorts, etc.?
Server settings, to a lesser degree, can affect this, as can database settings.
However, with no knowledge of what's happening, I can't tell you what's happening.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 7, 2023 at 12:46 pm
<!--more-->It isn't anything complex that I write which causes the error. Just a simple
select column a,b,c,d from TableName
The table is pretty large
December 7, 2023 at 12:55 pm
For something like this, assuming no other objects involved, it's not a view, you're not running some kind of monitoring, then, my suggestion is as follows. You have very little memory. All pages for a query have to be read into memory before returning them as part of a query. When there isn't enough memory, the disk is used to store stuff temporarily. Sure sounds like the problem. Probably. I'm still largely guessing since I have so little data to go on.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 7, 2023 at 1:01 pm
Do you have any other local drives with available space?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
December 7, 2023 at 3:28 pm
Is your tempdb database on the C: drive? If so, that could theoretically cause the problem.
By the way, 9GB is a very tiny amount of free space on a drive. You really should get a larger drive anyway.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 7, 2023 at 4:29 pm
Are you running a select query that returns millions of rows to your screen so you can look at them? if so, chances are that your pc is storing the data as temporary files in AppData folders. If this is the case, then adding top (n) will allow you to look at the table data without trying to store it all in memory.
December 7, 2023 at 9:02 pm
thanks for all the reponses!
Do you have any other local drives with available space?
Yes, I do. This is my work machine and I connect through a virtual machine. I did contact helpdesk about this at first. Their response was to clean up my disk space
Is your tempdb database on the C: drive? If so, that could theoretically cause the problem.
By the way, 9GB is a very tiny amount of free space on a drive. You really should get a larger drive anyway.
Yes, it is . Not sure how I can change it. The capacity of the drive in questions is about 155 GB. I am not sure how it got completely filled up. When I perform a disk clean up on my C drive I get that 9 gigs of free space
Are you running a select query that returns millions of rows to your screen so you can look at them? if so, chances are that your pc is storing the data as temporary files in AppData folders. If this is the case, then adding top (n) will allow you to look at the table data without trying to store it all in memory.
The table has a million plus rows. I get the error even when I write a query that filters the result set. Having said that, when I last checked I wasn't have a problem with a simple 1000 row result table. Only that it was slower than usual
December 7, 2023 at 10:38 pm
Look at the files on the drive and see if there is a large, obsolete file(s) (perhaps old backups?).
Depending on the version of SQL Server, you might also be able to use page compression to make the data size on disk much smaller (at the cost of CPU).
You've told us so little of your setup, or the query, that we have to be very general in what we suggest to do.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 7, 2023 at 11:13 pm
Hi,
I am not very familiar about how sql server works behind the scenes but could it be that my query results are being saved somewhere on my local drive ?
If you are using SSMS and the result set is huge, it can consume a significant amount of local disk space when displayed in the Results pane.
December 8, 2023 at 2:41 am
Look at the files on the drive and see if there is a large, obsolete file(s) (perhaps old backups?).
Depending on the version of SQL Server, you might also be able to use page compression to make the data size on disk much smaller (at the cost of CPU).
You've told us so little of your setup, or the query, that we have to be very general in what we suggest to do.
I wish I knew more about the setup. I just work off a virtual machine setup. When work provided me the laptop when I started , I just installed SSMS and with the default options selected
As for the query, it isn't anything too complex. Even a query with a coupe of filters cause a disk space error after a few attempts. I did read somewhere online about pointing the evironment variable to a different folder location with more space. I did that but hasn't helped much. I need to check it again to see if I did it right
Thanks
December 8, 2023 at 2:58 am
ScottPletcher wrote:Look at the files on the drive and see if there is a large, obsolete file(s) (perhaps old backups?).
Depending on the version of SQL Server, you might also be able to use page compression to make the data size on disk much smaller (at the cost of CPU).
You've told us so little of your setup, or the query, that we have to be very general in what we suggest to do.
I wish I knew more about the setup. I just work off a virtual machine setup. When work provided me the laptop when I started , I just installed SSMS and with the default options selected
As for the query, it isn't anything too complex. Even a query with a coupe of filters cause a disk space error after a few attempts. I did read somewhere online about pointing the evironment variable to a different folder location with more space. I did that but hasn't helped much. I need to check it again to see if I did it right
Thanks
It would help if you told us how many rows your query returns, is it the number of rows returned that causes the disk to fill up?
Is the virtual Windows machine that you are running on your laptop and is it the virtual machine size that is growing by 9GB?
What is the name and where is/are the file(s) that are growing to 9GB?
What is the exact error message you are getting?
December 8, 2023 at 3:47 am
masterelaichi wrote:ScottPletcher wrote:Look at the files on the drive and see if there is a large, obsolete file(s) (perhaps old backups?).
Depending on the version of SQL Server, you might also be able to use page compression to make the data size on disk much smaller (at the cost of CPU).
You've told us so little of your setup, or the query, that we have to be very general in what we suggest to do.
I wish I knew more about the setup. I just work off a virtual machine setup. When work provided me the laptop when I started , I just installed SSMS and with the default options selected
As for the query, it isn't anything too complex. Even a query with a coupe of filters cause a disk space error after a few attempts. I did read somewhere online about pointing the evironment variable to a different folder location with more space. I did that but hasn't helped much. I need to check it again to see if I did it right
Thanks
It would help if you told us how many rows your query returns, is it the number of rows returned that causes the disk to fill up?
Is the virtual Windows machine that you are running on your laptop and is it the virtual machine size that is growing by 9GB?
What is the name and where is/are the file(s) that are growing to 9GB?
What is the exact error message you are getting?
The exact message I get is "An error occurred when executing batch. There is not enough disk space"
Every time I run a query, I check the file properties of my windows folder (i.e, C: drive). Just now when I checked it says there is about 4.1GB of free space
I am running a query that returns 61787983 rows. This query has a windows function and a couple of filters. No join in this one
The virtual machine is not on my laptop. I am connected through citrix
December 8, 2023 at 10:05 am
Jonathan AC Roberts wrote:masterelaichi wrote:ScottPletcher wrote:Look at the files on the drive and see if there is a large, obsolete file(s) (perhaps old backups?).
Depending on the version of SQL Server, you might also be able to use page compression to make the data size on disk much smaller (at the cost of CPU).
You've told us so little of your setup, or the query, that we have to be very general in what we suggest to do.
I wish I knew more about the setup. I just work off a virtual machine setup. When work provided me the laptop when I started , I just installed SSMS and with the default options selected
As for the query, it isn't anything too complex. Even a query with a coupe of filters cause a disk space error after a few attempts. I did read somewhere online about pointing the evironment variable to a different folder location with more space. I did that but hasn't helped much. I need to check it again to see if I did it right
Thanks
It would help if you told us how many rows your query returns, is it the number of rows returned that causes the disk to fill up?
Is the virtual Windows machine that you are running on your laptop and is it the virtual machine size that is growing by 9GB?
What is the name and where is/are the file(s) that are growing to 9GB?
What is the exact error message you are getting?
The exact message I get is "An error occurred when executing batch. There is not enough disk space"
Every time I run a query, I check the file properties of my windows folder (i.e, C: drive). Just now when I checked it says there is about 4.1GB of free space
I am running a query that returns 61787983 rows. This query has a windows function and a couple of filters. No join in this one
The virtual machine is not on my laptop. I am connected through citrix
https://thelonedba.wordpress.com/2018/12/02/error-there-is-not-enough-space-on-the-disk/
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply