February 2, 2017 at 1:41 pm
Evening Guys,
DBCC ChecTable has come back with the following unpleasant looking message:
"
Msg 8909, Level 16, State 1, Line 139
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (9:44555703) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
CHECKTABLE found 0 allocation errors and 1 consistency errors not associated with any single object.
DBCC results for '_ACTIVITY_GEN'.
Msg 8928, Level 16, State 1, Line 139
Object ID 720721620, index ID 47, partition ID 72058824004665344, alloc unit ID 72058824045101056 (type In-row data): Page (9:44555702) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Line 139
Table error: Object ID 720721620, index ID 47, partition ID 72058824004665344, alloc unit ID 72058824045101056 (type In-row data), page (9:44555702). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12584969 and -4.
Msg 8976, Level 16, State 1, Line 139
Table error: Object ID 720721620, index ID 47, partition ID 72058824004665344, alloc unit ID 72058824045101056 (type In-row data). Page (9:44555702) was not seen in the scan although its parent (1:45527653) and previous (9:44555701) refer to it. Check any previous errors.
Msg 8928, Level 16, State 1, Line 139
Object ID 720721620, index ID 47, partition ID 72058824004665344, alloc unit ID 72058824045101056 (type In-row data): Page (9:44555703) could not be processed. See other errors for details.
Msg 8980, Level 16, State 1, Line 139
Table error: Object ID 720721620, index ID 47, partition ID 72058824004665344, alloc unit ID 72058824045101056 (type In-row data). Index node page (1:45527653), slot 23 refers to child page (9:44555703) and previous child (9:44555702), but they were not encountered.
Msg 8978, Level 16, State 1, Line 139
Table error: Object ID 720721620, index ID 47, partition ID 72058824004665344, alloc unit ID 72058824045101056 (type In-row data). Page (9:44568576) is missing a reference from previous page (9:44555703). Possible chain linkage problem.
There are 16555206 rows in 1986734 pages for object "_ACTIVITY_GEN".
CHECKTABLE found 0 allocation errors and 6 consistency errors in table '_ACTIVITY_GEN' (object ID 720721620).
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKTABLE (_ACTIVITY_GEN).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
"
Is there any way to identify the rows that are effected here?
This table contains a lot of (soon to be) archived data, so I am hoping the data falls within that range.
Also, what does repair_allow_data_loss do? I mean how does the data loss occur -- does it just delete offending rows?
Cheers
Alex
February 2, 2017 at 1:52 pm
Identity the rows can be extremely difficult.
First check to see if it's a nonclus or clus index/heap. If the errors are in a nonclus index, simply drop and re-create that index.
SELECT *
FROM sys.partitions
WHERE partition_id = 72058824004665344
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 2, 2017 at 2:16 pm
Silly question, I can't quite work it out -- how do I get the name of index id 47.. I thought it would be in sys.sysindexes but its not... I'm a bit confused
Cheers!
February 2, 2017 at 2:22 pm
Ah, found.... wrong table name!
SELECT * FROM sys.indexes WHERE object_id = 720721620 and index_id = 47
February 2, 2017 at 2:23 pm
Oops, sorry, I should have included that part.
SELECT *
FROM sys.indexes
WHERE index_id = 47
AND object_id = OBJECT_ID('_ACTIVITY_GEN')
Great, though, that it was index #47, that should be an easy fix.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 2, 2017 at 2:29 pm
To be fair Scott, I should have known to query that!
So, is this, do you think, indicative of a disk/memory/controller problem that I should be worried about? OR is possible that bad luck plays a part?
February 2, 2017 at 2:37 pm
I wouldn't worry about a one-time occurrence (although you should review whether or not you are on the latest SP/patch for that SQL instance).
If it happens again, then you would want to apply patches/maintenance -- to both SQL and Windows, if needed -- to avoid it from continuing.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 2, 2017 at 4:23 pm
Thanks again Scott. It's slated for upgrade 'any time now' actually to SQL Server 2016 -- the end goal to bring all these acquired servers onto a uniform version. Right now we range from 2008 R1 + R2 to 2016
February 3, 2017 at 8:18 am
Try dropping the index you identified and recreating it (not rebuild. Drop and then create as two separate operations), then run checktable again and see if the errors are gone.
If not, post the output of checktable
Have you checked the other tables in the database? If not I strongly suggest you run a full CheckDB as soon as possible.
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