We help IT Professionals succeed at work.

Inconsistent Metadata SQL Server 2005

In SQL Server 2005

Whenever I run a:
ALTER DATABASE Solar SET SINGLE_USER WITH ROLLBACK IMMEDIATE
and then
DBCC CheckDB (Solar , REPAIR_ALLOW_DATA_LOSS)

I get the following messages:
Msg 8930, Level 16, State 3, Line 1
Database error: Database 17 has inconsistent metadata. This error cannot be repaired and prevents further DBCC processing. Please restore from a backup.
Msg 8921, Level 16, State 1, Line 1
Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.

Is there a way to fix the inconsistent metadata or fix the tempdb without restoring from backup?
Comment
Watch Question

Scott PletcherSenior DBA
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
First, you have to make sure tempdb is not out of space, causing a false error.

If the db really is bad, you can force it back to a useable state and then export data from all the tables you can.  If a table is not readable, you will need to go to a backup, or spend money on a tool to recover the data.

Author

Commented:
Scott, sorry for not knowing this but how do I check and see if tempdb is out of space which may or may not cause the false error.

Commented:
check the drive where tempdb is installed whether it has any space available or not?
You can find out where tempdb is installed using sysaltfile catalog.
The in sql server management studio, connect to the server, right click on system database->tempdb->properties. Check the files and autogrowth type. Check if autogrowth is enabled or not?

Author

Commented:
TempDBA: Thanks, that seemed like a silly question after I looked at it. Yes I have Autogrowth enabled.

Commented:
and what about the current size of tempdb database and the free space on the drive?

Author

Commented:
TempDBA: I currently have 25 Gigs available on this drive and the files are:

tempdb.mdf:                8,192KB
and templog.ldf:          768KB

So that is not the issue.

Author

Commented:
Scott: when you say force it back to a useable state what do you mean? I currently can view 99 percent of the tables, what would be the fastest way to export and import all of the tables?
Commented:
you can write click on the database in management studio-> task-> export  
and then use the wizard to do the rest of the things. Its pretty much self-explanatory.

Or if you have any hot backup of the database(recent one), you can just restore that and test. If it still have the problem then you need to script the objects and data. Rest all I will leave on Scott .

Author

Commented:
Thanks TempDBA
Senior DBA
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
You can do:

EXEC sp_resetstatus 'Solar'

That will allow you to fully read the db even if SQL has it marked as "Suspect" (which it probably should by this point).

Yes, then use the Wizard and/or your own queries to get the data out of the db.

You can script out the entire db schema [which, btw, is not really accurate based on my testing, but it's close].  Then separately copy the data over to it.

If you hit a table that's too damaged to copy, just document it and skip it.

Hopefully you'll be able to get the vast majority of the contents into a new db.

Then you can DROP the old db, and rename the new name back to the original name.

Bingo, back in business!

Author

Commented:
Thank you Scott and TempDBA. These were great answers! Its greatly appreciated.

Author

Commented:
Thanks gang really helps.