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

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

    I get the following error when doing a variety of basic queries on other databases:

    "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."

    This doesn't make any sense since they are set to auto grow and there is plenty of disk space to do so.


    Both data and transaction files of tempdb are set to:
    "Automatically grow file" is checked
    "Maximum file size" is set to "Unrestricted file growth"
    Growth rate of 10%

    Both tempdb data file and transaction file are on D: but all drives have ample space:
    c: 25 GB free
    D: 69 GB free
    E: 175 GB free

    sp_spaceused returns the following for tempdb:

    name size unallocated space
    tempdb 4.00 MB 1.45 MB

    reserved data index_size unused
    568 KB 176 KB 304 KB 88 KB

    sp_helpdb returned for tempdb:

    tempdb
    4.00 MB sa 2 Apr 23 2004 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=539, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsAutoCreateStatistics, IsAutoUpdateStatistics 80

    This is with SQL Server 2000 (Personal Edition)
    Product Version: 8.00.760 (SP3)

    Is this some strange limitation of Personal Edition? Is it worth upgrading to Standard Edition?

  2. #2
    Join Date
    Sep 2003
    Posts
    522
    doesn't have anything to do with edition, it's your transaction. if it affects a lot of data, and in your case if work tables are needed to be created (this happens in tempdb) for sorting or grouping purposes, or if your indexes created with sort_in_tempdb, the data device will continue to grow for as much as it's needed based on your increment setting (default of 10% is not a good idea) once the limit is reached but the transaction has not completed, - it gets rolled back and the allocated size goes back to almost 0. if the "auto shrink" option is set, upon completion of rollback the allocated size goes down too. this explains why your sp_spaceused shows such a small number.

  3. #3
    Join Date
    Oct 2003
    Posts
    268
    Quote Originally Posted by ms_sql_dba
    doesn't have anything to do with edition, it's your transaction. if it affects a lot of data, and in your case if work tables are needed to be created (this happens in tempdb) for sorting or grouping purposes, or if your indexes created with sort_in_tempdb, the data device will continue to grow for as much as it's needed based on your increment setting (default of 10% is not a good idea) once the limit is reached but the transaction has not completed, - it gets rolled back and the allocated size goes back to almost 0. if the "auto shrink" option is set, upon completion of rollback the allocated size goes down too. this explains why your sp_spaceused shows such a small number.
    Bingo! The query actually caused tempdb to use the whole 27GB available on the C: drive, failed, shrunk to a few MB, and gave the illusion that space wasn't an issue. I moved tempdb to the E: drive, and it works perfectly.

    Thanks!

Posting Permissions

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