September 10, 2010 at 6:06 am
We recently bought a new business unit. Before we fully take over the IT system, one of the database server crushed that caused torn page corruption in the system table and they don't have any backup (yes, I know what you would say as I was wordless when I was told about that).
The DBCC CHECKDB returned the following:
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.
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.
Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error: torn page (expected signature: 0xaaaaaaaa; actual signature: 0x5556aaaa). It occurred during a read of page (1:7917) in database ID 13 at offset 0x00000003dda000 in file 'E:\Test DBs\ADS_SAS.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
The output of DBCC PAGE for page (1:7917) is following: (it could not output the full page info due to corruption, note error at the end)
PAGE: (1:7917)
BUFFER:
BUF @0x040C8AA8
bpage = 0x066EC000 bhash = 0x00000000 bpageno = (1:7917)
bdbid = 13 breferences = 0 bUse1 = 1901
bstat = 0x1c00809 blog = 0x999a2159 bnext = 0x00000000
PAGE HEADER:
Page @0x066EC000
m_pageId = (1:7917) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x8100
m_objId (AllocUnitId.idObj) = 60 m_indexId (AllocUnitId.idInd) = 1 Metadata: = Unavailable in offline DB
m_prevPage = (1:70) m_nextPage = (1:7918) pminlen = 17
m_slotCnt = 2 m_freeCnt = 543 m_freeData = 7645
m_reservedCnt = 0 m_lsn = (18281:350:4) m_xactReserved = 0
m_xdesId = (0:0) m_ghostRecCnt = 0 m_tornBits = -2145251326
Allocation Status
GAM (1:2) = ALLOCATED SGAM (1:3) = NOT ALLOCATED PFS (1:1) = 0x40 ALLOCATED 0_PCT_FULL
DIFF (1:6) = NOT CHANGED ML (1:7) = NOT MIN_LOGGED
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
The object_name(60) is sysobjvalues
I know the normal way to fix this will be to restore from good backup or put the db in emergency mode and pump out the data to a new database. I just wonder is anyone know if there is any way I can try to repaire the corrupted system table (view)?
The above output is from SQL2005. The database is SQL2000. I managed to get it up in SQL2005 emergency mode. As there are corruptions on other 2 user tables that prevent me to pump data out or try any repaire on them, I was hopeing to "fix" the system table corruption so I can run DBCC CHECKDB that may help to fix corruption on the user tables. Any suggestion/help is much appreciated.
September 10, 2010 at 6:23 am
The only way you are going to recover from this is to restore from a clean backup. If you don't have a clean backup, then the only remaining possibility is to export what data you can (some will almost certainly fail) and script all objects (some may well fail) and recreate the database with what you managed to extract.
Corruption to system tables is not repairable in any way.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 10, 2010 at 7:32 am
Thanks Gail, Paul Randal demoed fix a system table corruption (user partially deleted record from system table in SQL2000 db) and you helped someone in a post by delete a stats record to fix a system table corruption error (your cool tricks select * from sys.stats where ...). I understand the corruption difference between my situation and the above 2 mention. I just hope there might be a way to work around my problem (or I can try) as well. Thanks again for your reply.
September 10, 2010 at 7:47 am
John Liu-329005 (9/10/2010)
Thanks Gail, Paul Randal demoed fix a system table corruption (user partially deleted record from system table in SQL2000 db)
Yup, that's for when there just a missing reference in the system tables (orphaned child record). Typically caused by someone deleting from the system tables, and not by IO corruption. That's not what you have here.
and you helped someone in a post by delete a stats record to fix a system table corruption error (your cool tricks select * from sys.stats where ...).
Yes, and that's for when only the stats blob is damaged, something that's not critical and can be removed without impact...
Both of those are very special cases and far from the norm for system table damage.
I understand the corruption difference between my situation and the above 2 mention. I just hope there might be a way to work around my problem (or I can try) as well. Thanks again for your reply.
There is not. You have a damaged page in a critical system table. Short of opening the file in a hex editor and manually editing it to have the correct values (which maybe 10 people in the world are capable of doing) there is no fix for your corruption problem.
Script, export and hope that most of the DB is recoverable.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 10, 2010 at 8:21 am
You could offer to call MS and get support, but my guess is that they would tell you what Gail has listed here.
September 10, 2010 at 8:50 am
Thanks Gail & Steve. I fully understand. This question is answered and closed.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply