July 27, 2009 at 4:32 am
Hello Experts,
I have had the following errors appear in a couple of tables, but I am unsure if this is actual corruption or not, I am also unsure of which fix to use, would repair rebuild work in this case? Or am I looking at something more serious than that? Could I get away with rebuilding the indexes? I am running SQL 2000 on Service Pack 4.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 459148681: Errors found in text ID 7353204736 owned by data record identified by RID = (1:19360:2) PROCESSID = 4551 and STEP = 4.
Server: Msg 2533, Level 16, State 1, Line 1
Table error: Page (1:2347) allocated to object ID 459148681, index ID 255 was not seen. Page may be invalid or have incorrect object ID information in its header.
Server: Msg 2533, Level 16, State 1, Line 1
Table error: Page (1:2348) allocated to object ID 459148681, index ID 255 was not seen. Page may be invalid or have incorrect object ID information in its header.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 459148681. The text, ntext, or image node at page (1:2348), slot 1, text ID 7353204736 is referenced by page (1:19360), slot 2, but was not seen in the scan.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 1385771994: Errors found in text ID 15904342016 owned by data record identified by RID = (1:50810:21) ID = 19721.
Server: Msg 8961, Level 16, State 1, Line 1
Table error: Object ID 1385771994. The text, ntext, or image node at page (1:143), slot 0, text ID 15904342016 does not match its reference from page (1:143), slot 1.
Server: Msg 8961, Level 16, State 1, Line 1
Table error: Object ID 1385771994. The text, ntext, or image node at page (1:143), slot 0, text ID 15904342016 does not match its reference from page (1:143), slot 1.
Server: Msg 8974, Level 16, State 1, Line 1
Text node referenced by more than one node. Object ID 1385771994, text, ntext, or image node page (1:143), slot 0, text ID 15904342016 is pointed to by page (1:143), slot 1 and by page (1:7872), slot 3.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1385771994. The text, ntext, or image node at page (1:143), slot 1, text ID 7155548160 is not referenced.
Thank you in advance for any help you can give, I have limited experience in this type of situation.
Regards,
D.
July 27, 2009 at 5:29 am
Yup, you've got corruption, specifically in the Text/NText/Image column in that table. That's not an index that you can just rebuild.
Do you have a clean (uncorrupt) backup of this database? Based on what I can see, if we repair, we will be losing data, specifically some of the blob pages (which contain the text/ntext/image data) and anything in them.
Can you please run this and post the entire output of it. There's some bits I need to see that are missing from your post.
DBCC CHECKDB (< Database Name > ) WITH NO_INFOMSGS, ALL_ERRORMSGS
Also, since this is SQL 2000, it won't harm to run this as well.
DBCC CHECKCATALOG (< Database Name > )
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
July 27, 2009 at 6:14 am
Hello Gail, Thanks for getting back.
I have just inherited this DB, so for the moment I would assume that no backup is available as nobody will have looked at this for ages I expect, the output from the first command you kindly posted is...
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 459148681: Errors found in text ID 7353204736 owned by data record identified by RID = (1:12653:85) PROCESSID = 4433 and STEP = 3.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 459148681: Errors found in text ID 7353204736 owned by data record identified by RID = (1:19360:2) PROCESSID = 4551 and STEP = 4.
Server: Msg 2533, Level 16, State 1, Line 1
Table error: Page (1:2347) allocated to object ID 459148681, index ID 255 was not seen. Page may be invalid or have incorrect object ID information in its header.
Server: Msg 2533, Level 16, State 1, Line 1
Table error: Page (1:2348) allocated to object ID 459148681, index ID 255 was not seen. Page may be invalid or have incorrect object ID information in its header.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 459148681. The text, ntext, or image node at page (1:2348), slot 1, text ID 7353204736 is referenced by page (1:19360), slot 2, but was not seen in the scan.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 1385771994: Errors found in text ID 15904342016 owned by data record identified by RID = (1:50810:21) ID = 19721.
Server: Msg 8961, Level 16, State 1, Line 1
Table error: Object ID 1385771994. The text, ntext, or image node at page (1:143), slot 0, text ID 15904342016 does not match its reference from page (1:143), slot 1.
Server: Msg 8961, Level 16, State 1, Line 1
Table error: Object ID 1385771994. The text, ntext, or image node at page (1:143), slot 0, text ID 15904342016 does not match its reference from page (1:143), slot 1.
Server: Msg 8974, Level 16, State 1, Line 1
Text node referenced by more than one node. Object ID 1385771994, text, ntext, or image node page (1:143), slot 0, text ID 15904342016 is pointed to by page (1:143), slot 1 and by page (1:7872), slot 3.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1385771994. The text, ntext, or image node at page (1:143), slot 1, text ID 7155548160 is not referenced.
CHECKDB found 0 allocation errors and 5 consistency errors in table 'tablename1' (object ID 459148681).
CHECKDB found 0 allocation errors and 5 consistency errors in table 'tablename2' (object ID 1385771994).
CHECKDB found 0 allocation errors and 10 consistency errors in database 'database_name'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (database_name ).
And the second is...
DBCC results for 'Database_Name'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Presumably the fact that nothing came of the second command is good news?
Thank you for the help.
Regards,
D.
July 27, 2009 at 6:36 am
Not really good news. You've still got corruption. Just wanted to be sure that it wasn't worst than what I could see. On SQL 2005, CheckDB includes the checks run by CheckCatalog, on SQL 2000 it doesn't.
Ok, without a backup the only really fix here is to run checkDB with the REPAIR_ALLOW_DATA_LOSS option. It will cause data loss. Specifically, you'll probably lost the text/ntext/image data from two, possibly 3 rows.
Two rows from the table with the ID 459148681, where PROCESSID = 4433 and STEP = 3 and where PROCESSID = 4551 and STEP = 4. It shouldn't remove the rows themselves, just their Text/ntext/image data.
Possibly 1 row from the table with the ID 1385771994. Can't tell which row there.
That should be all the data that gets deleted. Take a backup before you run repair, if you don't like what it did, at least you'll have a backup from before.
When you have some downtime, take the DB into single user mode and run DBCC CheckDB on this DB with the REPAIR_ALLOW_DATA_LOSS option. It'll probably take longer than the normal checkDB did. No way to say how long.
Once you're got that sorted, make sure you set up regular cehckDB and CheckCatalog jobs so that any probelms are detected early next time.
It may be worth checking out the IO subsystem of this server, see if there are any errors or warnings, outdated drivers, failing hardware, etc. Corruption is usually the fault of hardware, usually the IO subsystem.
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
July 27, 2009 at 6:48 am
Thank you Gail,
Would definatly take a backup beforehand, I'll check out the rows and see whats what, doesnt look as if its too bad.
Thank you for your help, your a bit of a legend.
kind regards,
D.
July 27, 2009 at 6:56 am
When you check those rows, don't include the text/ntext/image column. If you do, you're more likely to get a severity 24 error than to get any data. Query the table and don't include the text/ntext/image column in the select list.
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 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply