I was running a query in SQL Server Management Studio (SSMS), nothing too complicated, and got this error, unexpectedly:
An error occurred while executing batch. Error message is: There is not enough space on the disk.
There’s plenty of space on the server, so what’s the problem? The problem is that we’re running a query in SSMS, and SSMS caches resultsets to your %TEMP% directory. And there’s no way to configure that, other than changing where %TEMP% actually *is*.
Just to demonstrate the problem, navigate to %TEMP% – it’s buried somewhere in the user profile AppData directory, so it’s quicker just to open a new explorer window and type %TEMP% into the navigation bar:
However, no sign of any files containing anything useful. Open a command window at this location – quick way is to just put “cmd” into the Navbar and hit return:
That opens up a command window in the right location. Look for hidden files:
There’s the culprit (OK, it’s only 14KB here, bu tit’s a small query just to prove a point…)
To really prove the point, on this particular server, I can do it just by running this:
USE [msdb]; SELECT * FROM [dbo].[backupset] AS [bs1], [dbo].[backupset] AS [bs2], [dbo].[backupset] AS [bs3];
…but that would risk crashing the server. So I won’t do that.
What do we learn from this, though? The main points (for me):
- Don’t run SSMS on servers
- Make sure there’s plenty of disk space