tempdb too big

Posted on 2000-03-07
Last Modified: 2010-05-18
My server (SQL 7) has a rather large hard drive so I never had any problems with space until today. For some reason my tempdb is all of a sudden 11GB. I don't know what the problem is but I really need to free up all that space. Shrink database didn't work and if i check the database in enterprize manager it's only 1.28 MB used (of 11,614.63MB)

I did a DBCC CHECKDB on tempdb and I got the following messages:

Server: Msg 8999, Level 16, State 1, Line 0
Database tempdb allocation errors prevent further CHECKDB processing.
Server: Msg 8906, Level 16, State 1, Line 0
Page (1:114) in database ID 2 is allocated in the SGAM (1:3) and PFS (1:1), but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED   0_PCT_FULL'.
Server: Msg 8905, Level 16, State 1, Line 0
Extent (1:31992) in database ID 2 is marked allocated in the GAM, but no SGAM or IAM has allocated it.

I have no idea what to do with them!

Thanks for the help!
Question by:rreinmuller
  • 2
  • 2

Author Comment

ID: 2593474
Adjusted points to 100

Accepted Solution

wqw earned 100 total points
ID: 2593494
did try to stop/start SQL server?


Expert Comment

ID: 2593740

Read following article it will explain what to do.

Hope this helps.

Expert Comment

ID: 2593747

Sorry : in the above article search for Error 8906
Severity Level 16


Author Comment

ID: 2593757
Thanks for the quick responses!
I had to wait until most of the users were out then I restarted SQL . That restored my tempdb to a more reasonable 56MB (not 11GB) and it is not corrupted anymore.

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Using Aggregate Functions to Count 3 32
My Query is not giving correct result. Please help 5 29
Sql query 107 27
MSDN Licensing query 5 0
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
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.

910 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

23 Experts available now in Live!

Get 1:1 Help Now