August 13, 2008 at 12:57 pm
Hey Guys--
I've only been working as a DBA for a short time with my company.
I've noticed that, when running a dbcc checkdb on one of our databases (~70gb in size) it produces the below errors (bottom of this post). The TW_EM_Backup table consistency error is no big deal luckily, but the others concern me. The database has apparently been operational with these errors for some time, so using an old backup isn't really a viable option.
I've been trying to pinpoint the data that is corrupted by running the below command:
dbcc page(twdata, 1, 1520954, 3)
go
But this just returns an error. If I lower the page number, the dbcc page goes through, but doesn't return any results. Anything I'm doing wrong here? I'm not sure what the index ID being -1 entails.
Are there any alternatives to doing a repair with data loss?
Many thanks in advance for taking a look at this.
Msg 8944, Level 16, State 16, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 323304136114176 (type Unknown), page (1:1520954), row 0. Test (VarColOffsets + (int)(sizeof (class VarColOffset) * nVarCols) <= (nextRec - pRec)) failed. Values are 76136 and 800.
Msg 8944, Level 16, State 16, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 323304136114176 (type Unknown), page (1:1520954), row 0. Test (VarColOffsets + (int)(sizeof (class VarColOffset) * nVarCols) <= (nextRec - pRec)) failed. Values are 76136 and 800.
CHECKDB found 0 allocation errors and 2 consistency errors not associated with any single object.
Msg 8928, Level 16, State 1, Line 1
Object ID 1300967761, index ID 0, partition ID 85260223184896, alloc unit ID 85260223184896 (type In-row data): Page (1:1520954) could not be processed. See other errors for details.
CHECKDB found 0 allocation errors and 1 consistency errors in table 'TW_EM_Backup' (object ID 1300967761).
CHECKDB found 0 allocation errors and 3 consistency errors in database 'TWDATA'.
August 13, 2008 at 1:39 pm
There should have been another line in the checkDB output saying what level was required to fix. What did it say?
Since you have no good backup, the only way to fix this is to run checkDB with the repair level that it specified. If it specified that repair_allow_data_loss is required, then you are going to have to run that and you will lose data. No idea how much or what.
This is why regular checkDBs are recommended. It's very important to discover corruption problems early as they can be easily fixed.
Try doing the DBCC Page with the last parameter of 2. That dumps the page contents out raw (binary). It may not be very readable and there are no guarantees that it will work.
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
August 13, 2008 at 1:45 pm
Yep, it was repair_allow_data_loss.
I will take a backup of it this weekend and try the repair; will see how that goes.
Does the index being specified as -1 mean that this is a data page that's corrupt?
August 13, 2008 at 1:56 pm
To see the DBCC PAGE output you have to enable trace flag 3604; DBCC TRACEON(3604).
https://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/10/625659.aspx
August 13, 2008 at 2:21 pm
Thanks Todd.
Looks like it can't even dump the data on the page. Interesting.
Output for this page:
PAGE: (1:1520954)
BUFFER:
BUF @0x04913448
bpage = 0x22302000 bhash = 0x00000000 bpageno = (1:1520954)
bdbid = 11 breferences = 0 bUse1 = 3703
bstat = 0x1c00009 blog = 0x159a2159 bnext = 0x00000000
PAGE HEADER:
Page @0x22302000
m_pageId = (1:1520954) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x20
m_objId (AllocUnitId.idObj) = 638262320 m_indexId (AllocUnitId.idInd) = 0
Metadata: AllocUnitId = 41829159403520 Metadata: PartitionId = 0
Metadata: IndexId = -1 Metadata: ObjectId = 0 m_prevPage = (0:0)
m_nextPage = (0:0) pminlen = 20 m_slotCnt = 12
m_freeCnt = 45 m_freeData = 8115 m_reservedCnt = 0
m_lsn = (514241:33777:172) m_xactReserved = 0 m_xdesId = (0:187733697)
m_ghostRecCnt = 0 m_tornBits = 306719649
Allocation Status
GAM (1:1022464) = ALLOCATED SGAM (1:1022465) = NOT ALLOCATED
PFS (1:1520544) = 0x43 ALLOCATED 95_PCT_FULL DIFF (1:1022470) = NOT CHANGED
ML (1:1022471) = 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.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
August 13, 2008 at 2:23 pm
Just share some of my experiences here.
Consistency error may mean either a failure bit (incorrect data) on hard disk or improper data allocation, as far as I know.
If it is a hard disk issue, you may do hard disk scan;
If it is improper data allocation, you may try to increase the size of data file and see whether or not the error goes away. I came across this case in SQL 7.
If you would like to repair your database, just add parameters after DBCC command. But do not forget to have a right backup before doing so.
August 13, 2008 at 2:54 pm
j2r (8/13/2008)
Does the index being specified as -1 mean that this is a data page that's corrupt?
No. It means SQL hasn't got the vaguest clue what this page is or should be. Data page would be index ID 0 or 1, nonclustered index 2-254, LOB data 255. Index ID outside of the range 0-255 is invalid
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
August 13, 2008 at 5:41 pm
I was actually able to fix this without doing a repair.
It turns out all these corruption errors were related to the TW_EM_Backup table, which was just a test table and not in production.
I tried just opening this table, and it failed near the end, citing the torn page from my checkdb error output.
Since this was just a test table anyway I just got rid of it, and now my dbcc checkdbs are clean.
I realize I was lucky this time, and am going to schedule checkdbs nightly for all databases from now on.
August 14, 2008 at 1:13 am
That's good to hear.
Daily is perhaps overkill. CheckDB is a very intensive operation. Running it weekly, over a weekend is probably adequate.
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply