Solved

Temp db Error

Posted on 2004-10-21
277 Views
Last Modified: 2008-01-09
ADO Error #1: 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. Source:Microsoft OLE DB Provider for SQL Server (Number: -2147217900 | NativeError: 1101 | SQLState: 42000)

Can anyone please help me fix the above issue.

many thanks.
0
Question by:crishna1
    6 Comments
     
    LVL 32

    Accepted Solution

    by:
    You are out of space, either on the drive containing the physical TempDB files, or because you limited tempDB's size.

    Depending on your situation, you can:
    (1) Stop and restart SQL Server (clears tempDB)
    (2) shrink tempdb
    (3) clear space on tempdb's drive
    (4) allow TempDB to grow
    0
     

    Author Comment

    by:crishna1
    1.Restarted the server
    2.shrunked the tempdb
    3. there is 5 gb space on tempdb's drive
    4. auto grow is checked

    still the same error!
    0
     
    LVL 32

    Expert Comment

    by:bhess1
    What is running that is giving the error?  Is it possible that you have a process attempting to allocate more than 5GB of data space?
    0
     
    LVL 6

    Assisted Solution

    by:Duane Lawrence
    I have run into this exact error before.

    I have actually spoke to Microsoft SQL Server developers to get this corrected in Yukon.

    When you make a maintainance plan, you can check all databases and shrink all databases.  But the option to shrink the tempdb does not work.  Years ago under SQL Server 7, it had to be rebooted every several days.  Now, SQL Server 2000 will stay up for over a year.  If you do not manually shrink the tempdb or put a job to do it, this will happen and cause you to restart the server.  When SQL Server is restarted it rebuilds the tempdb from scratch.

    DBCC SHRINKDATABASE (tempdb, 10)

    Put the above line in a job and your done, now your MS SQL Server will stay up for 2 years.  My longest is 2 years and 3 months, with the clock still ticking.

    <removed by Lunchy>
    0
     
    LVL 8

    Assisted Solution

    by:sigmacon
    I once ran into a weird problem with the file groups for tempdb, where the default group could not be made to grow once it was set to a fixed size. Usually, tempdb stays small, unless suddenly somebody is doing something unusual. Maybe that's what happening to you, so no matter how many restarts or shrinks, you may not be able to store whatever temporary data you have in your current temp table. If nothing else helps, create a second file group on a different hard drive (which recommended for tempdb anyway). I have not checked the following code, its more to give you an idea. You can also add a seconde file group using the GUI tools ...

    ALTER DATABASE tempdb
      CREATE FILEGROUP TEMPSEC
    go

    ALTER DATABASE tempdb
      ADD FILE
       (
              NAME = "temp_sec"
             ,FILENAME = "drive:\folder\tempsec.ndf"
             ,SIZE = 1GB
             ,MAXSIZE = UNLIMITED
             ,FILEGROWTH = 25%
       )
      TO FILEGROUP TEMPSEC
    0
     
    LVL 8

    Expert Comment

    by:sigmacon
    Did any of our suggestions help? What's the status on this?
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    The steps for moving the system databases to a new location are documented in the following technical article: http://msdn.microsoft.com/en-us/library/ms345408(v=SQL.90).aspx. However sometimes after the moving process is finished, though SQL i…
    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.

    857 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now