rreinmuller
asked on
tempdb too big
Hi!
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!
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!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi,
Read following article it will explain what to do.
http://www.microsoft.com/TechNet/sql/manuals/diag/part3/75528c13.asp
Hope this helps.
Read following article it will explain what to do.
http://www.microsoft.com/TechNet/sql/manuals/diag/part3/75528c13.asp
Hope this helps.
Hi,
Sorry : in the above article search for Error 8906
Severity Level 16
Luck.
Sorry : in the above article search for Error 8906
Severity Level 16
Luck.
ASKER
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.
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.
ASKER