Could not allocate a new page for database 'TEMPDB' because of insufficient disk space in filegroup 'DEFAULT'. 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.

I have had this error several times on different servers, and here are the steps that I recommend following to find and resolve the problem.

Step 1 – Track down the source of the error

One of the things I found with this error, is that it doesn’t tell you where the error is actually occurring. In one instance I found the problem was not on the server presenting error, nor on the server it was reading from, but on a third SQL instance that one part of the query was reading from.

For that reason, I recommend that the first step is to inspect the query that is being run to eliminate the possibility that the error is coming from another server.

If you find that the error is coming from a different server, then repeat Step 1.

Step 2 – Restart the SQL Service

It is possible that a restart is just clearing unused space in tempdb and only looks like it has fixed the issue. It would be a good idea to verify if the restart worked.

If you are unable, or not allowed to, restart the service (until a maintenance window), then try moving on to the next step.

Step 3 – Check the Basics

Check to confirm that there is enough disk space for tempdb to grow. This step should be an obvious one, but it needs to be ruled out before moving on.

Step 4 – Set the Autogrowth explicitly

By default, the tempdb is installed with 4 database files all belonging to the PRIMARY filegroup. The PRIMARY filegroup is set to ‘Autogrow All Files’. So when you look at the files in the properties window, it look like this…

Even though it looks like autogrowth is disabled this is normal (try Testing the Growth of the tempdb on a healthy server and you will see that the files do grow).

But I’ve found that enabling the autogrowth, even to a small amount, fixes the error.

Testing the Growth of the tempdb

You can use this script to show that the files in tempdb are actually growing.

USE tempdb
GO

SELECT name, size/128.0 AS Size
FROM sys.database_files;

DECLARE @temp_table TABLE (ID INT IDENTITY PRIMARY KEY, Name NVARCHAR(100), LargeText NVARCHAR(MAX), LargerText NVARCHAR(MAX))
DECLARE @randText NVARCHAR(MAX) = (SELECT TOP (5000) CHAR(ABS(CHECKSUM(NEWID())) % 26 + ASCII('A')) FROM sys.all_objects FOR XML PATH(''))

DECLARE @id INT = 0
DECLARE @iterations INT = 200000
WHILE @id < @iterations
BEGIN
	INSERT INTO	@temp_table (Name, LargeText)
	VALUES ('My Text Name ' + CAST(@id AS NVARCHAR(6)), @randText)
	SET @id+=1
END

UPDATE @temp_table SET LargerText = Name + ':' + LargeText + ':' + @randText;


SELECT name, size/128.0 AS Size
FROM sys.database_files;

The script creates a temp table and adds data to it.

If tempdb doesn’t force a size increase then increate the number of iterations.

If there is still a problem you will get an error like…

Could not allocate space for object  'dbo.@temp_table'.'PK__#A120A9E__3214EC27FE76A5CD' 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.

However, if everything is ok, you should see something like this, which shows all the database files growing.

Shrinking the Files

Shrinking database files is seen as a bad idea by some people (see Brent’s article here)

Although none of the issues they have with shrinking the files should affect the tempdb, and in my experience hasn’t caused any issues, but I haven’t seen it fix the problem either, well, temporarily maybe, but the problem soon comes back because the files are not growing.

So if you want to shrink them do so, but be aware that even if the error goes away, it might come back.

USE tempdb
GO
SELECT name, size/128.0 AS Size, CAST(FILEPROPERTY(name, 'SpaceUsed') AS  INT)/128.0 AS SpaceUsed, size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS  INT)/128.0 AS AvailableSpaceInMB
FROM sys.database_files;

DBCC SHRINKFILE (tempdev, 200);
DBCC SHRINKFILE (temp2, 200);
DBCC SHRINKFILE (temp3, 200);
DBCC SHRINKFILE (temp4, 200);

SELECT name, size/128.0 AS Size, CAST(FILEPROPERTY(name, 'SpaceUsed') AS  INT)/128.0 AS SpaceUsed, size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS  INT)/128.0 AS AvailableSpaceInMB
FROM sys.database_files;

Viewing the Objects in tempdb

I’ll add this query because it adds to the transparency of tempdb, but it’s never actually done me much good.

SELECT t.name
       , SUM(a.used_pages) * 8 AS KBUsed
       , p.rows AS [Rows]
       , t.create_date
FROM tempdb.sys.tables t
    INNER JOIN tempdb.sys.partitions p ON t.object_id = p.object_id
    INNER JOIN tempdb.sys.allocation_units a ON a.container_id = p.partition_id
GROUP BY t.name, p.rows, t.create_date