An error occurred while executing batch. Error message is: There is not enough space on the disk.

Question

Hello all,

I execute a T-SQL query, which will retrieve large amount of data, however, I get an error message after a few seconde.

The error message is :

An error occurred while executing batch. Error message is: There is not enough space on the disk.

Who can tell me the reason and waht's the steps to solve this issue ?

Really thanks.

---

Thanks, Jed deng

Answer 1

Hi Jed,

Check the available disk space where TempDb resides, as you are retrieving large numbers of record and if there is any sort, hash join or spool is happening, tempdb will be will be used.. 

---

- Chintak (My Blog)

Answer 2

Where are your log and data files placed? Please confirm those directories have enough space.

For log file, you can either shrink the log file or move other unnecessary files from the directory to get more space.

About your query, how many rows are you trying to retrieve?

---

Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

Answer 3

The other two posters suggested that the problem with tempdb or you data/log files. This is not the case here. The error message relates to the client-side of things. Apparently SSMS needs to store data on disk. I don't recall exactly when this happens, but I seem to recall that the files appear in you %TEMP% directory.

I would suggest that your correct action is to clean up the disk. Not the least there may be a lot of junk in your %TEMP% and %TMP% directories.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 4

Hi Erland,

         Very true, it could be an issue. I have come across those kind of issues when I was working with LDAP.

---

Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

Answer 5

The other two posters suggested that the problem with tempdb or you data/log files. This is not the case here. The error message relates to the client-side of things. Apparently SSMS needs to store data on disk. I don't recall exactly when this happens, but I seem to recall that the files appear in you %TEMP% directory.

I would suggest that your correct action is to clean up the disk. Not the least there may be a lot of junk in your %TEMP% and %TMP% directories.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Thanks Erland, I was not aware of this earlier. Now, I am able to find out one of the resons for slowness of SSMS gird. Also, I have just checked it with process monitor, SSMS is creating tmp%.tmp files..

---

- Chintak (My Blog)

Answer 6

I would suggest that your correct action is to clean up the disk. Not the least there may be a lot of junk in your %TEMP% and %TMP% directories.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Hi Sommarskog,

Could you please tell me the where is the location for temp file? I am using SQL 2008 R2

---

Thanks, Jed deng

Answer 7

In run command, type %TEMP%.

---

Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

Answer 8

You can find out the definition of your environment variables in Control Panel->System->Advanced->Environment Variables.

The exact path can vary depending on your version Windows.

You can also view them (but not edit on a permanent basis) from a command-line window by running SET, or "SET TEMP" to view that particular variable.
Beware that there are two environment variables, TEMP and TMP.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 9

In my case is little different , when I am running select statement on of the table which contains 200000000 data the C: derive is filling fast, after few minute query returns error saying that "An error occurred while executing batch. Error message is: There is not enough space on the disk." .. When I looked all the data file and log file for all the data base including system database, there is no data file or log file belongs to C: derive. I have increase the size of C: derive and got solution.

But still question is open that what SQL server doing with C: derive? Can any one help me please. OR share the link where I can know what happening when sql server  read huge data from table, and how it need C: derives.

Thanks

---

Regards Vikas Pathak

Answer 10

[There have been some problems with the NNTP bridge which I use for posting today. So in case, the problem still persists: this post is from Erland Sommarskog, whatever it says at the bottom.]

But still question is open that what SQL server doing with C: derive? Can any one help me please. OR share the link where I can know what happening when sql server  read huge data from table, and how it need C: derives.

SQL Server is not doing anything on your C drive. If SQL Server is running on a different machine, it cannot even access the disk.

But SQL Server Management Studio needs to buffer the data somewhere, and since SSMS runs on your disk, it will use space in your %TEMP% or %TMP% directory. Trying to receive a 200 million result set to SSMS does not seem like a good idea to me.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 11

Thanks a lot Erland Sommarskog,

 The problem was with the SSMS, nothing was in C:\ derive , But I found the SSMS is holding that all data in grid that occupying the space. that's why just after closing that ssms window  free space   getting      free . I have just made some more free space on C:\ derive and suggested team  to not to query select * from big table instead use select count(*) or use select top 10.

---

Regards Vikas Pathak