Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2003
    Posts
    268

    Unanswered: Could not allocate new page for database 'TEMPDB'?

    "Could not allocate new page for database 'TEMPDB'. There are no more pages available in filegroup DEFAULT. Space can be created by dropping objects, adding additional files, or allowing file growth."

    I get this error when running a query on another database. But why?

    Both data and transaction files on TEMPDB are set to "automatically grow file" and "unrestricted file growth" and there is 70GB of free space on the disk drive. Shouldn't the files just grow? Why would this happen?

  2. #2
    Join Date
    Mar 2004
    Posts
    14

    you create temporary table in your query?

    because temporary table store in tempdb

  3. #3
    Join Date
    Oct 2003
    Posts
    268
    Quote Originally Posted by netcoder
    because temporary table store in tempdb
    Thanks for the reply. I don't understand what you are saying but it sounds like you know what the issue is. Could you please elaborate a little bit?

  4. #4
    Join Date
    Mar 2004
    Posts
    14

    reply

    You say :I get this error when running a query
    you are running a query , can you show me the query ?
    do you create temporary table in your query?

  5. #5
    Join Date
    Oct 2003
    Posts
    268
    Quote Originally Posted by netcoder
    You say :I get this error when running a query
    you are running a query , can you show me the query ?
    do you create temporary table in your query?
    No, I'm not explicitly doing anything with TEMPDB.

    My query is very straight forward:

    SELECT TOP 100 SampleDescriptor, COUNT(*) RepCount FROM DataSamples GROUP BY SampleDescriptor ORDER BY RepCount DESC

    On my dev system, with 30 test records, this works fine.

    On the production server with 188 million records (yes, a LOT of records!), this fails with the TEMPDB error that I mentioned.

    I'm trying a simple: SELECT COUNT(*) FROM DataSamples now. It's been running for over 30 minutes and still going. I wonder if it will hit a similar error...

  6. #6
    Join Date
    Sep 2003
    Posts
    54
    When you get the error, have you checked the size of tempdb (both data and log) and free space of the hard drive?

    I assume that tempdb is located in the hard drive with 70GB free space.

  7. #7
    Join Date
    Oct 2003
    Posts
    268
    Yes, of course. The size of both tempdb data and transaction files are 2MB and they are located on the drive with 68GB free. All drives on that system have plenty of space (although for tempdb, D: should be the only one that matters):

    C: has 25 GB free
    D: has 68 GB free
    E: has 124 GB free

  8. #8
    Join Date
    Sep 2003
    Posts
    54
    What do sp_spaceused and sp_helpdb return?

    BTW, which version and service pack of SQL Server?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •