Temp db Error

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.
crishna1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brendt HessSenior DBACommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
crishna1Author Commented:
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
Brendt HessSenior DBACommented:
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 Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Duane LawrenceCommented:
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
sigmaconCommented:
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
sigmaconCommented:
Did any of our suggestions help? What's the status on this?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.