Not enough disk space error

  • 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

    • This topic was modified 2 months, 2 weeks ago by  masterelaichi.
  • 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.

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

     

     

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

  • Do you have any other local drives with available space?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • 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) A socialist is someone who will give you the shirt off *someone else's* back.

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

  • thanks for all the reponses!

     

    Phil Parkin wrote:

    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

     

    ScottPletcher wrote:

    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

    Ed B wrote:

    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

     

     

     

    • This reply was modified 2 months, 2 weeks ago by  masterelaichi.
  • 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) A socialist is someone who will give you the shirt off *someone else's* back.

  • masterelaichi wrote:

    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.

  • 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

  • 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?

  • 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

     

  • masterelaichi wrote:

    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