Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Temp db Error

Posted on 2004-10-21
6
Medium Priority
?
287 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
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 …
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.

609 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