Failure SQL query insufficient disk space

Question

Msg 1101, Level 17, State 10, Line 12 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.

What does this mean in plain English.

Solution 1

I've found that the normal cause of such explosive growth of TempDB is a query, either ad hoc or in a stored procedure, that has an unexpected many-to-many join in it that some refer to as an "Accidental Cross Join". Behind the scenes, it can create litterally billions of internal rows that end up living in "work" tables that live in TempDB.

The fix isn't to simply allocate more disk space. The fix is to find which query is the cause of the problem and fix it. Otherwise, you'll be stuck in a never ending cycle of having to restart SQL Server, etc, etc.

And, no... you don't have to check to see if TempDB is in the "SIMPLE" recovery mode because you can't set it to anything else. Try it and see.

Solution 2

It means your tempdb database filled up

you can

1. Restart the SQL Server service, this will recreate the tempdb database
2. Add another file on another disk with more space
3. Shrink the log file of tempdb

See Dealing with the could not allocate new page for database 'TEMPDB'. There are no more pages available in filegroup DEFAULT error message for more details

Comments

[Ken White]

This question is clearly tagged SQL Server, which is not Firebird.