?
Solved

Temp db Error

Posted on 2004-10-21
6
Medium Priority
?
289 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
Comment
Question by:crishna1
6 Comments
 
LVL 32

Accepted Solution

by:
Brendt Hess earned 750 total points
ID: 12374049
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
ID: 12374563
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:Brendt Hess
ID: 12374776
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
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
LVL 6

Assisted Solution

by:Duane Lawrence
Duane Lawrence earned 450 total points
ID: 12374780
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
sigmacon earned 300 total points
ID: 12379111
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
ID: 12470428
Did any of our suggestions help? What's the status on this?
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

593 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