Failure SQL query insufficient disk space

sql sql-server join view tempdb
JsonStatham picture JsonStatham · Jan 20, 2012 · Viewed 37.1k times · Source

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.

Answer

Jeff Moden picture Jeff Moden · Jan 21, 2012

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.

Related questions

SQL update from one Table to another based on a ID match

I have a database with account numbers and card numbers. I match these to a file to update any card numbers to the account number, so that I am only working with account numbers. I created a view linking the …

Read More
Difference between JOIN and INNER JOIN

Both these joins will give me the same results: SELECT * FROM table JOIN otherTable ON table.ID = otherTable.FK vs SELECT * FROM table INNER JOIN otherTable ON table.ID = otherTable.FK Is there any difference between the statements in performance …

Read More
Can I use CASE statement in a JOIN condition?

The following image is a part of Microsoft SQL Server 2008 R2 System Views. From the image we can see that the relationship between sys.partitions and sys.allocation_units depends on the value of sys.allocation_units.type. So to …

Read More