Can You Fail To Spill To tempdb?
One of my colleagues reached out to me recently about a production issue where a SQL Server Agent job had failed with this error message:
Msg 1105, Level 17, State 2, Line 15
Could not allocate space for object ‘dbo.SORT temporary run storage: 140737513062400’ in database ‘tempdb’ because the ‘PRIMARY’ filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
I fully expected this to be a scheduled maintenance task, like index rebuilds or statistics updates. I’ve seen this error before in those contexts (rebuilding large indexes in tempdb
, or updating statistics with FULLSCAN
).
In the latest of a lifelong series of situations where my intuition failed me, my expectation was incorrect. In fact, the job was an ETL. And the step that failed wasn’t creating temp tables, or indexes, or anything that I might have expected. I thought that was pretty intriguing, and thus this blog post.
Major Spillage
Feel free to stand and salute while reading that heading 😎
Using the small, 2010 version of the Stack Overflow sample database, I’ll run this query:
SELECT p.Id FROM dbo.Posts p ORDER BY p.Body;
Here’s the execution plan:
As you can see from the little warning icon, it spills to tempdb
. Massively:
Further slowing things down, my tempdb
files actually grew repeatedly during this operation:
“Optimizing” tempdb
For the purposes of this demo, I’m now going to shrink all of my tempdb
files back down to their default size (8 MB on my machine):
DBCC SHRINKDATABASE(N'tempdb')
And then disable autogrow:
USE [master]
GO
ALTER DATABASE [tempdb] MODIFY FILE (NAME = N'tempdev', FILEGROWTH = 0);
ALTER DATABASE [tempdb] MODIFY FILE (NAME = N'temp2', FILEGROWTH = 0);
ALTER DATABASE [tempdb] MODIFY FILE (NAME = N'temp3', FILEGROWTH = 0);
ALTER DATABASE [tempdb] MODIFY FILE (NAME = N'temp4', FILEGROWTH = 0);
ALTER DATABASE [tempdb] MODIFY FILE (NAME = N'temp5', FILEGROWTH = 0);
ALTER DATABASE [tempdb] MODIFY FILE (NAME = N'temp6', FILEGROWTH = 0);
ALTER DATABASE [tempdb] MODIFY FILE (NAME = N'temp7', FILEGROWTH = 0);
ALTER DATABASE [tempdb] MODIFY FILE (NAME = N'temp8', FILEGROWTH = 0);
ALTER DATABASE [tempdb] MODIFY FILE (NAME = N'templog', FILEGROWTH = 0);
In real life, autogrow really was disabled on the tempdb
files. They had been evenly expanded to fill the dedicated tempdb
drive.
Trying to Spill
So what happens when I try to run that query now? I can tell you that it does not magically get a bigger memory grant. Nor does it find some way to use the limited tempdb
space available to do the sorting.
As you might expect from all the build up, the query fails very quickly with an error similar to the one from the beginning of this post:
Msg 1101, Level 17, State 1, Line 1
Could not allocate a new page for database ‘tempdb’ because of insufficient disk space in filegroup ‘PRIMARY’. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
My local demo here is on SQL Server 2019 CTP 3.0. The real issue occurred on SQL Server 2012, which may explain the difference in the error messages.
Don’t Assume
It’s easy to jump to conclusions based on similar symptoms. This was just a little reminder, for me at least, to make sure and understand the context and details of a situation before recommending a solution.
Some options in our case would be to
- allocate more space to
tempdb
, - tune the queries involved to avoid the spills, or
- examine the other concurrent activity on the server to see if its
tempdb
usage can be reduced (or rescheduled to not coincide with the ETL).
Thanks for reading!