12

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.

Jimmy Sawczuk
  • 13,488
  • 7
  • 46
  • 60
JsonStatham
  • 9,770
  • 27
  • 100
  • 181
  • 9
    There is no need to be so abrupt. The poster is obviously asking for clarification on why this is happening. – general exception Jan 21 '12 at 21:37
  • 1
    @generalexception - As the message is in fact very clear it helps if they explain what they don't understand. Are we working from a starting point where they don't know what `tempdb` is even or why it is related to their query? – Martin Smith Jan 22 '12 at 14:56
  • 2
    Apologies Martin, you should assume I know what tempdb is, what I was getting at was what are the general causes of this error message, i will try to be crystal clear next time so as not to cause confusion or doubt. From now one if i paste an error message I will go through each part and say whether I understand it or not. – JsonStatham Jan 23 '12 at 09:31
  • 1
    Again, apologies, where would we be if we didnt have people like you to point these things out... – JsonStatham Jan 23 '12 at 10:30

3 Answers3

13

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.

Jeff Moden
  • 3,271
  • 2
  • 27
  • 23
8

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

SQLMenace
  • 132,095
  • 25
  • 206
  • 225
-5
  1. Stop the Citrix System Monitoring Agent service
  2. Stop the Firebird Server - CSMInstance service
  3. Delete the RSDATR.FDB firebird database located at C:\Documents and Settings\All Users\Application Data\Citrix\System Monitoring\Data
  4. Restart the services
Mohammad Afaque Siddiqui
  • 15
  • 1