When I run DBCC CHECKDB, I got the following error. I only pick which the one that have an error. Due to there is no good backup and I don't want to run DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS as yet cause the app still running ok at this stage, if I do detach the database and copy the MDF and LDF somewhere and do the re-attach back to second server, will it a good idea to cover this by doing the DBCC CHECKDB repair_allow_data_loss on the second server perhaps? My concern is if I detach back on the first server, will it be back to retach again on first server?
Server: Msg 8966, Level 16, State 2, Line 1
Could not read and latch page (1:1261) with latch type UP. 23(error not found) failed.
DBCC results for 'db01210101_2'.
CHECKDB found 0 allocation errors and 1 consistency errors not associated with any single object.
DBCC results for 'sysobjects'.
There are 1438 rows in 24 pages for object 'sysobjects'.
DBCC results for 'CAT_TypesTexts'.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 693577509: Errors found in text ID 15553789952 owned by data record identified by RID = (1:1632:0) ScheduleHistoryID = 11632.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 693577509: Errors found in text ID 15579480064 owned by data record identified by RID = (1:3488:1) ScheduleHistoryID = 11711.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 693577509: Errors found in text ID 15605825536 owned by data record identified by RID = (1:3489:0) ScheduleHistoryID = 11808.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 693577509: Errors found in text ID 15678046208 owned by data record identified by RID = (1:3490:30) ScheduleHistoryID = 12078.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 693577509: Errors found in text ID 15678570496 owned by data record identified by RID = (1:3490:32) ScheduleHistoryID = 12080.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 693577509: Errors found in text ID 15680405504 owned by data record identified by RID = (1:3490:39) ScheduleHistoryID = 12087.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 693577509: Errors found in text ID 15680929792 owned by data record identified by RID = (1:3490:41) ScheduleHistoryID = 12089.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 693577509: Errors found in text ID 15682502656 owned by data record identified by RID = (1:3491:3) ScheduleHistoryID = 12095.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 693577509: Errors found in text ID 15682764800 owned by data record identified by RID = (1:3491:4) ScheduleHistoryID = 12096.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 693577509: Errors found in text ID 15683026944 owned by data record identified by RID = (1:3491:5) ScheduleHistoryID = 12097.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 693577509: Errors found in text ID 15684075520 owned by data record identified by RID = (1:3491:9) ScheduleHistoryID = 12101.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 693577509: Errors found in text ID 15685124096 owned by data record identified by RID = (1:3491:13) ScheduleHistoryID = 12105.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 693577509: Errors found in text ID 15685713920 owned by data record identified by RID = (1:3491:16) ScheduleHistoryID = 12108.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 693577509: Errors found in text ID 15685976064 owned by data record identified by RID = (1:3491:17) ScheduleHistoryID = 12109.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 693577509: Errors found in text ID 15687155712 owned by data record identified by RID = (1:3491:23) ScheduleHistoryID = 12115.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 693577509: Errors found in text ID 15689842688 owned by data record identified by RID = (1:3491:27) ScheduleHistoryID = 12119.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 693577509: Errors found in text ID 15691415552 owned by data record identified by RID = (1:3491:32) ScheduleHistoryID = 12124.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 693577509: Errors found in text ID 15693381632 owned by data record identified by RID = (1:3491:39) ScheduleHistoryID = 12131.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 693577509: Errors found in text ID 15696527360 owned by data record identified by RID = (1:3491:51) ScheduleHistoryID = 12143.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 693577509: Errors found in text ID 15697182720 owned by data record identified by RID = (1:3491:54) ScheduleHistoryID = 12146.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 693577509: Errors found in text ID 15700983808 owned by data record identified by RID = (1:3491:69) ScheduleHistoryID = 12161.
Server: Msg 2533, Level 16, State 1, Line 1
Table error: Page (1:1261) allocated to object ID 693577509, 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 693577509. The text, ntext, or image node at page (1:1261), slot 0, text ID 15685713920 is referenced by page (1:3491), slot 16, but was not seen in the scan.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 693577509. The text, ntext, or image node at page (1:1261), slot 1, text ID 15687155712 is referenced by page (1:3491), slot 23, but was not seen in the scan.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 693577509. The text, ntext, or image node at page (1:1261), slot 2, text ID 15553789952 is referenced by page (1:1632), slot 0, but was not seen in the scan.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 693577509. The text, ntext, or image node at page (1:1261), slot 3, text ID 15579480064 is referenced by page (1:3488), slot 1, but was not seen in the scan.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 693577509. The text, ntext, or image node at page (1:1261), slot 4, text ID 15605825536 is referenced by page (1:3489), slot 0, but was not seen in the scan.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 693577509. The text, ntext, or image node at page (1:1261), slot 5, text ID 15685976064 is referenced by page (1:3491), slot 17, but was not seen in the scan.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 693577509. The text, ntext, or image node at page (1:1261), slot 6, text ID 15689842688 is referenced by page (1:3491), slot 27, but was not seen in the scan.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 693577509. The text, ntext, or image node at page (1:1261), slot 7, text ID 15691415552 is referenced by page (1:3491), slot 32, but was not seen in the scan.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 693577509. The text, ntext, or image node at page (1:1261), slot 8, text ID 15693381632 is referenced by page (1:3491), slot 39, but was not seen in the scan.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 693577509. The text, ntext, or image node at page (1:1261), slot 9, text ID 15696527360 is referenced by page (1:3491), slot 51, but was not seen in the scan.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 693577509. The text, ntext, or image node at page (1:1261), slot 10, text ID 15697182720 is referenced by page (1:3491), slot 54, but was not seen in the scan.
DBCC results for 'ScheduleHistory'.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 693577509. The text, ntext, or image node at page (1:1261), slot 11, text ID 15700983808 is referenced by page (1:3491), slot 69, but was not seen in the scan.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 693577509. The text, ntext, or image node at page (1:1261), slot 13, text ID 15678046208 is referenced by page (1:3490), slot 30, but was not seen in the scan.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 693577509. The text, ntext, or image node at page (1:1261), slot 14, text ID 15678570496 is referenced by page (1:3490), slot 32, but was not seen in the scan.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 693577509. The text, ntext, or image node at page (1:1261), slot 15, text ID 15680405504 is referenced by page (1:3490), slot 39, but was not seen in the scan.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 693577509. The text, ntext, or image node at page (1:1261), slot 16, text ID 15680929792 is referenced by page (1:3490), slot 41, but was not seen in the scan.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 693577509. The text, ntext, or image node at page (1:1261), slot 17, text ID 15682502656 is referenced by page (1:3491), slot 3, but was not seen in the scan.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 693577509. The text, ntext, or image node at page (1:1261), slot 18, text ID 15682764800 is referenced by page (1:3491), slot 4, but was not seen in the scan.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 693577509. The text, ntext, or image node at page (1:1261), slot 19, text ID 15683026944 is referenced by page (1:3491), slot 5, but was not seen in the scan.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 693577509. The text, ntext, or image node at page (1:1261), slot 29, text ID 15684075520 is referenced by page (1:3491), slot 9, but was not seen in the scan.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 693577509. The text, ntext, or image node at page (1:1261), slot 30, text ID 15685124096 is referenced by page (1:3491), slot 13, but was not seen in the scan.
There are 5619 rows in 75 pages for object 'ScheduleHistory'.
CHECKDB found 0 allocation errors and 43 consistency errors in table 'ScheduleHistory' (object ID 693577509).
CHECKDB found 0 allocation errors and 44 consistency errors in database 'db01210101_2'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (db01210101_2 ).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Let me start off by saying that under normal circumstance I would just restore from the last good backup. However in this case it appears as though the last good backup was sometime last August ... arg! After much yelling at the person responsible I've been attempting to get my blood pressure below 200 and see what data is recoverable.
First off, this was a RAID5 system that failed 1 drive. Secondly, before we got someone in there to replace said drive it failed a second drive and the system went down. We managed to massage the system back online but it appears that there is some corruption as a result which is no surprise.
I've done DB repairs in the past and it hasn't been too bad, but this time it is looking a little gnarly.
I've kicked everyone off the server and tried starting SQLServer several different ways.
I tried starting the service normally and then flagging the bad DB into single user mode with "ALTER DATABASE foo SET SINGLE_USER". I then did a select * from sysdatabases to make sure it took, which it did. I also tried starting the whole SQLServer in single user mode from the command line, "SQLServr -m".
I can run "DBCC CHECKDB('foo')" and I get a long ugly list of allocation errors. I posted it to a link as the 1349 lines returned is a little long: http://chrisnet.net/sqlbad/dbcc_checkdb.txt
But when I attempt to bite the bullet and destroy data in an attempt to put things back together with: "DBCC CHECKDB('foo', REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS" I get: Server: Msg 7919, Level 16, State 2, Line 1 Repair statement not processed. Database needs to be in single user mode. DBCC execution completed. If DBCC printed error messages, contact your system administrator.
But yet the database is in single user mode, according to everything I check check on. Is this just SQL's way of telling me the corruption is too severe to be repaired? No output is displayed in the shell cmd window like it is for a successful DBCC either (when running sqlservr -m).
I followed the advice of Paul Randal, but Im stumped as I am not able to determin what the corruption issues are. This is SQL 2000 and the database is a Solomon database that was recently upgraded to 6.5. the error I get when running the DBCC checkdb is as follows:
Server: Msg 8966, Level 16, State 1, Line 1 Could not read and latch page (1:18645) with latch type SH. sysindexes failed. DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator. DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Now, the bad news. I am a bit of a novice and have picked this up from someone who left my company. It appears the latch error has been around for some time and only reared up when I instituted a new back up system that runs a dbcc check befor backing up. I don't think I have any clean backups.
I have a few extremely large databases in SQL Server 6.5 sp3 (soon to be 5a - but we won't talk about that!!) NT 4.0 sp4 (about 10 GIG each). I don't have a big window of down-time in order to do any maintenance. Does anyone know of a way to be able to run dbcc checkdb or other dbcc's that I can run to verify the database yet complete within a reasonable amount of time? The last time dbcc checkdb was run, it was started Friday night and still not complete Sunday night. Over a weekend, I may have up to a 24 hour maintenance window.
Any suggestions would be appreciated. Thanks! Toni
Hai , When I ran DBCC CHECK DB of userdatabase, its reporting along with usual messages as Descriptor for system table '8' in database '8' not found in the descriptor hash table. I could'nt understand being familiar error encounterd . Any one will appreciate for the help
I recently took over a SQL server with 300 MB of data. I am relatively new to SQL 6.5 and have been reading that DBCC checkdb and checkalloc should be run at least once per week. Apparently the person before me never ran any of those checks. Is not running the database consistency checks for so long going to present a problem? has anyone run into problems when running those checks? Any advise is greatly appreciated.
I ran "dbcc checkdb(MCMSdb) with no_infomsgs" and I get the following: Server: Msg 8946, Level 16, State 12, Line 2 Table error: Allocation page (1:274992) has invalid PFS_PAGE page header values. Type is 0. Check type, object ID and page ID on the page.
What cane be done to correct this problem? Can this error prevent a user from connecting to the database?
I ran checkdb and found 4 error message on the db.. it seen like same object.. can anyone tell me what it is.. and how can i fix it? thanks !!!!
1. Server: Msg 8976, Level 16, State 1, Line 35 Table error: Object ID 2094630505, index ID 1. Page (1:809859) was not seen in the scan although its parent (1:77885) and previous (1:809767) refer to it. Check any previous errors. 2. Server: Msg 8978, Level 16, State 1, Line 35 Table error: Object ID 2094630505, index ID 1. Page (1:809860) is missing a reference from previous page (1:809859). Possible chain linkage problem. 3. Server: Msg 8976, Level 16, State 1, Line 35 Table error: Object ID 2094630505, index ID 1. Page (1:1453795) was not seen in the scan although its parent (1:1453347) and previous (1:1453796) refer to it. Check any previous errors. 4. Server: Msg 8978, Level 16, State 1, Line 35 Table error: Object ID 2094630505, index ID 1. Page (1:1453801) is missing a reference from previous page (1:1453795). Possible chain linkage problem.
Does anybody know if the results of DBCC CHECKDB are stored anywhere? If yes, where? Also, if you don't select "attempt to repair minor problems" option when you set up the maintenance plan, will SQL Server let you know about any errors Integrity check encounters? If yes, where the erros can be found?
in the SQL 6.5 documentation it says when running the DBCC CHECKDB, you should make the database read Only or DBO use only. Do you guys know if SQL 6.5 locks rows while this runs? In SQL 7.0/2000 it only locks the schema.
I am using windows nt40 and sql server 6.5 on a DEC ALPHA and accidentlly started a dbcc checkdb. Is it possible to stop the process with out damaging the database?
When running dbcc checkdb from my workstation(nt) I recieve some of the output and then I get "Connection Broken" this is on a 6.5 machine with the service pack 5, what could be causing my ODBC connection to drop during the proccess of running checkdb?
How can I make the dbcc checkdb fail so I can get the errors displayed in the report log? DBCC Checkdb is the step in the integrity job that I run once a month. What I am trying to do is when the dbcc checkdb fails for any reason, I want to get notified so I can correct the problem. I don't want to use repair fast or any other repair parameters that you can select when you run dbcc checkdb function because most of my dbs are 24x7.
I have a job set up that runs the dbcc checkdb and puts the results into a table. Then I run a query against this table to check the results of dbcc checkdb. If there are errors, I get a page that lets me know that there are some problems. When I ran this job on my production server, the job failed stating that there are problems with data integrity. So I copied this db to a dev server and recreated the job just like I have in prod. The job completed successfully withou any errors. Can anybody tell me what to look at to figure out why it failed on the prod server?
Madhur writes "Last week DBCC CHECKDB <database name> took 2 Hrs to complete on a database. Today again i have started but with the following command: dbcc checkdb (<database name>, REPAIR_FAST ).
It is now running for more than 2.5 Hrs now.
Does the execution time increases even when the DB is consistent?
Can we cancel the execution in the middle? What consequences it may have on the db?
I have a db that is about 50Gigs. Last night it went into a suspect mode and I have not been able to get any of my tables to display. I am running the dbcc checkdb. I have been running it for over 7 hrs. Do you have any suggestions as to what I should do or do you have any ideas as to how long this process might take?
Ricardo writes "We are consistently getting this error. We have check the hardware and nothing appears in the checkdk, can you help.
Thanks
Ricardo
dbcc checkdb (virtualrequest) DBCC results for 'tblVRAnswers'. Server: Msg 8928, Level 16, State 1, Line 1 Object ID 370256524, index ID 3: Page (1:243518) could not be processed. See other errors for details. Server: Msg 8939, Level 16, State 1, Line 1 Table error: Object ID 370256524, index ID 3, page (1:243518). Test (IS_ON (BUF_IOERR, bp->bstat) &&bp->berrcode) failed. Values are 2057 and -1. There are 9608 rows in 106 pages for object 'tblVRAnswers'.
CHECKDB found 0 allocation errors and 2 consistency errors in database 'VirtualRequest'.
dbcc checkdb (virtualrequest) DBCC results for 'tblvrquestions'. Server: Msg 8952, Level 16, State 1, Line 1 Table error: Database 'VirtualRequest', index 'tblVRRequestedItems.IX_NC_tblVRRequestedItems_CurrentStatusID' (ID 784825958) (index ID 2). Extra or invalid key for the keys: Server: Msg 8956, Level 16, State 1, Line 1 Index row (1:214293:372) with values (CurrentStatusID = 3 and RequestedItemID = 413260) points to the data row identified by (). There are 6614 rows in 103 pages for object 'tblvrquestions'.
CHECKDB found 0 allocation errors and 1 consistency errors in database 'VirtualRequest'
dbcc checkdb (virtualrequest) DBCC results for 'tblvrquestions'. Server: Msg 8952, Level 16, State 1, Line 1 Table error: Database 'VirtualRequest', index 'tblVRRequestedItems.IX_NC_tblVRRequestedItems_CurrentStatusID' (ID 784825958) (index ID 2). Extra or invalid key for the keys: Server: Msg 8956, Level 16, State 1, Line 1 Index row (1:214293:372) with values (CurrentStatusID = 3 and RequestedItemID = 413260) points to the data row identified by (). There are 6614 rows in 103 pages for object 'tblvrquestions'.
CHECKDB found 0 allocation errors and 1 consistency errors in database 'VirtualRequest'."
My nightly maintenance task runs fine normally. Last night it didn't. When it got to DBCC CHECKDB for an audit database (it gets populated from triggers in other databases; one of the tables has over a million records), the live database server shut down and did not come back up. DBCC CHECKDB completed in 6 seconds with no errors. The next task in the sequence that would have run (but the log has no message of it completing or failing) was DBCC INDEXDEFRAG on that audit table with over a million records.
Even the OFF button on the 64-bit Dell server (SQL2005, Windows Server Enterprise x64 2003 SP1, 16 gig ram) did not work. There wasn't even the blue screen of death. It was just a blue screen. A plug out and plug back in was required.
The only recent configuration change has been that we have had Dell install a piece of disk-level replication software called Double Take.
When i run TKizers sp to check database integrity. I'm getting this error.
Executed as user: IAPESQLAdmin. ...536) DBCC results for 'sysobjects'. [SQLSTATE 01000] (Message 2536) There are 1364 rows in 23 pages for object 'sysobjects'. [SQLSTATE 01000] (Message 2593) DBCC results for 'sysindexes'. [SQLSTATE 01000] (Message 2536) There are 138 rows in 5 pages for object 'sysindexes'. [SQLSTATE 01000] (Message 2593) DBCC results for 'syscolumns'. [SQLSTATE 01000] (Message 2536) There are 5251 rows in 104 pages for object 'syscolumns'. [SQLSTATE 01000] (Message 2593) DBCC results for 'systypes'. [SQLSTATE 01000] (Message 2536) There are 26 rows in 1 pages for object 'systypes'. [SQLSTATE 01000] (Message 2593) DBCC results for 'syscomments'. [SQLSTATE 01000] (Message 2536) There are 2115 rows in 1112 pages for object 'syscomments'. [SQLSTATE 01000] (Message 2593) DBCC results for 'sysfiles1'. [SQLSTATE 01000] (Message 2536) There are 2 rows in 1 pages for object 'sysfiles1'. [SQLSTATE 01000] (Message 2593) DBCC results for 'syspermissions'. [SQLSTATE... The step failed.
I ran "dbcc checkdb(MCMSdb) with no_infomsgs" and I get the following:Server: Msg 8946, Level 16, State 12, Line 2Table error: Allocation page (1:274992) has invalid PFS_PAGE pageheader values. Type is 0. Check type, object ID and page ID on thepage.What cane be done to correct this problem?Can this error prevent a user from connecting to the database?
Hi, Appreciated someone able to provide me some clu on how to perfom DBCC CHECKDB. Is it necessary to set the database to "Read only" before execute DBCC CHECKDB?
I have a database that was handed to me which is getting the following errors:
DBCC CHECKDB ('DBName') returns: Server: Msg 8939, Level 16, State 106, Line 1 Table error: Object ID 1, index ID 0, page (1:37). Test (m_freeCnt == freeCnt) failed. Values are 680 and 788. Server: Msg 8966, Level 16, State 1, Line 1 Could not read and latch page (1:37) with latch type SH. sysobjects failed.
DBCC PAGE (DBName, 1, 37, 1) returns: Server: Msg 8939, Level 16, State 20, Line 1 Table error: Object ID 99, index ID 0, page (1:2). Test (m_slots[0].GetOffset () == PAGEHEADSIZE) failed. Values are 662 and 96. Server: Msg 8939, Level 16, State 1, Line 1 Table error: Object ID 99, index ID 0, page (1:2). Test (Align(m_slots[-1].GetOffset ()) == Align(m_slots[0].GetOffset () + sizeof(GAMHEADER) + sizeof(DataRecHdr))) failed. Values are 190 and 756. DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC PAGE (DBName, 1, 2, 1) returns: Server: Msg 8939, Level 16, State 20, Line 1 Table error: Object ID 99, index ID 0, page (1:2). Test (m_slots[0].GetOffset () == PAGEHEADSIZE) failed. Values are 662 and 96. Server: Msg 8939, Level 16, State 1, Line 1 Table error: Object ID 99, index ID 0, page (1:2). Test (Align(m_slots[-1].GetOffset ()) == Align(m_slots[0].GetOffset () + sizeof(GAMHEADER) + sizeof(DataRecHdr))) failed. Values are 190 and 756. Server: Msg 3624, Level 20, State 1, Line 1
And finally, DBCC CHECKTABLE (sysobjects) WITH NO_INFOMSGS, ALL_ERRORMSGS gives me: Server: Msg 8939, Level 16, State 106, Line 1 Table error: Object ID 1, index ID 0, page (1:37). Test (m_freeCnt == freeCnt) failed. Values are 680 and 788. Server: Msg 8966, Level 16, State 1, Line 1 Could not read and latch page (1:37) with latch type SH. sysobjects failed.
I've looked at the MDF with a hex editor and there's definitely data in there. I have the DB attached and in single user/emergency mode, but DBCC isn't having much luck. Help?
when running DBCC CHECKDB on a DB i get this error: Server: Msg 8966, Level 16, State 1, Line 1 Could not read and latch page (4:3457816) with latch type SH. sysindexes failed.
when running DBCC CHECKTABLE ('sysindexes') i get a little bit more info: Server: Msg 8966, Level 16, State 1, Line 1 Could not read and latch page (4:3457816) with latch type SH. sysindexes failed. Server: Msg 8944, Level 16, State 1, Line 1 Table error: Object ID 2, index ID 0, page (4:3457816), row 9. Test (!(hdr->r_tagA & (VERSION_MASK | RECTAG_RESV_A | RECTAG_RESV_B))) failed. Values are 105 and 193. CHECKTABLE found 0 allocation errors and 1 consistency errors in table 'sysindexes' (object ID 2).
I'm a bit clueless after seeing this and would like to know if someone has experienced this before and would be willing to offer some advice.
I am getting the following after kicking off DBCC CHECKDB 'DATABASE', REPAIR_ALLOW_DATA_LOSS) :
Executed as user: USER. Database 'TEST' consistency errors in sysobjects, sysindexes, syscolumns, or systypes prevent further CHECKDB processing. [SQLSTATE 42000] (Error 7995) Clustered index successfully restored for object 'syscolumns' in database 'TEST'. [SQLSTATE 01000] (Error 2592) DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Error 2528). The step failed.
I got error after running DBCC checkDB, the error was: ----------- [10] Database MyDB: Check Data and Index Linkage... [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 8921: [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKTABLE terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is corrupt. Check previous errors. [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 1 consistency errors not associated with any single object. [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 1 consistency errors in database 'MyDB'. [Microsoft][ODBC SQL Server Driver][SQL Server]repair_fast is the minimum repair level for the errors found by DBCC CHECKDB (MyDB ).
The following errors were found:
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKTABLE terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is corrupt. Check previous errors. [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 1 consistency errors not associated with any single object. [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 1 consistency errors in database 'MyDB'. [Microsoft][ODBC SQL Server Driver][SQL Server]repair_fast is the minimum repair level for the errors found by DBCC CHECKDB (MyDB ). ** Execution Time: 1 hrs, 44 mins, 34 secs ** ---------
Then after running DBCC CheckDB (MyDB, repair_fast), the error was: --------------- Server: Msg 8966, Level 16, State 2, Line 1 Could not read and latch page (1:1879304) with latch type SH. Latch failed. CHECKDB found 0 allocation errors and 1 consistency errors not associated with any single object. CHECKDB found 0 allocation errors and 1 consistency errors in database 'MyDB'. repair_fast is the minimum repair level for the errors found by DBCC CHECKDB (MyDB, repair_fast). --------
If I run DBCC CheckTbale for each table, there is no error on those tables.
I have a very intersting scenario occuring during a DBCC CheckDB, which causes the server and I/O bus to completely hang when using an ExtremeRAID3000 fibre card. Here is the full hardware scenario:
Two Windows 2000 Advanced Servers (clustered) w/2GB RAM each ExtremeRAID3000 card JBOD with Fibre Drives SQL2000 SP3 (have not tried SP4 yet)
I have been able to reproduce this on 3 different clusters. Each had different brands of memory/motherboard/hard drives. The only common hardware item was the ExtremeRAID3000.
Each clustered server runs about 400 databases, 24/7/365. I only reboot the computers about once every 3000 hours as the Windows Updates pile up and need to be applied.
If I run a DBCC CHECKDB on various databases, the command hangs and the I/O subsystem crashes, requiring a full reboot of both nodes in order to get the fibre I/O working again.
All other operations work fine, including: physical defragmenting the hard drive logical defragmentation of the databases full/Differential database backups chkdsk (fsutil /set dirty) failover multiple terrabytes of bytes read/written through SQL for weeks on end
I currently backup and restore the databases onto a different server each week, and run DBCC CHECKDB to verify them, which is how I know they are fine (plus the customer's don't complain of anything corrupt).
Any ideas what DBCC CHECKDB could possibly be doing to cause the I/O subsystem to go offline? This is not consistent either, I can run it on some databases some weeks, and other databases other weeks. It varies from time to time. But if I find one it crashes on, it will hang again if I run it again immediately after rebooting. So I haven't run DBCC CHECKDB's on the production servers with ExtremeRAID 3000's in over two years.
One final thing, I have several other clusters that do not use these cards, and they have no problems with DBCC CHECKDB.
Intelligent Design is Christianity Evolving due to Natural Selection.
1. How to fix these errors? 2. Are the records in the DB still good? 3. How to prevent this happening again?
The database server is running Windows 2000 sp4 and SQL 2000 sp3. It's running on the RAID5 in MS Cluster environment.
I really appreciate your assistance. Thank you.
The error message samples are:
Msg 8974, Sev 16: Text node referenced by more than one node. Object ID 837578022, text, ntext, or image node page (1:130912), slot 27, text ID 25207869603840 is pointed to by page (1:1392667), slot 2 and by page (1:1210230), slot 10. [SQLSTATE 42000] Msg 8961, Sev 16: Table error: Object ID 837578022. The text, ntext, or image node at page (1:130912), slot 30, text ID 25199404974080 does not match its reference from page (1:131226), slot 17. [SQLSTATE 42000] Msg 8974, Sev 16: Text node referenced by more than one node. Object ID 837578022, text, ntext, or image node page (1:130912), slot 30, text ID 25254380437504 is pointed to by page (1:1392667), slot 4 and by page (1:131226), slot 17. [SQLSTATE 42000] Msg 8964, Sev 16: Table error: Object ID 837578022. The text, ntext, or image node at page (1:134162), slot 23, text ID 25204615217152 is not referenced. [SQLSTATE 42000]
Msg 8990, Sev 16: CHECKDB found 0 allocation errors and 211 consistency errors in table 'TABLE_ONE' (object ID 837578022). [SQLSTATE 01000] Msg 2536, Sev 16: DBCC results for 'TABLE_TWO'. [SQLSTATE 01000]
Msg 8974, Sev 16: Text node referenced by more than one node. Object ID 853578079, text, ntext, or image node page (1:129320), slot 38, text ID 25496001314816 is pointed to by page (1:239021), slot 101 and by page (1:487008), slot 100. [SQLSTATE 42000] Msg 8961, Sev 16: Table error: Object ID 853578079. The text, ntext, or image node at page (1:129320), slot 39, text ID 25199327903744 does not match its reference from page (1:487008), slot 101. [SQLSTATE 42000] Msg 8974, Sev 16: Text node referenced by more than one node. Object ID 853578079, text, ntext, or image node page (1:129320), slot 39, text ID 25496001642496 is pointed to by page (1:239021), slot 102 and by page (1:487008), slot 101. [SQLSTATE 42000] Msg 8964, Sev 16: Table error: Object ID 853578079. The text, ntext, or image node at page (1:136454), slot 1, text ID 25031875559424 is not referenced. [SQLSTATE 42000] Msg 8964, Sev 16: Table error: Object ID 853578079. The text, ntext, or image node at page (1:136454), slot 2, text ID 25031875887104 is not referenced. [SQLSTATE 42000]
Msg 8964, Sev 16: Table error: Object ID 853578079. The text, ntext, or image node at page (1:136603), slot 2, text ID 25031875231744 is not referenced. [SQLSTATE 42000] Msg 8928, Sev 16: Object ID 853578079, index ID 255: Page (1:137476) could not be processed. See other errors for details. [SQLSTATE 42000] Msg 8939, Sev 16: Table error: Object ID 853578079, index ID 255, page (1:137476). Test (m_freeCnt == freeCnt) failed. Values are 357 and 525. [SQLSTATE 42000]
Msg 2593, Sev 16: There are 1972569 rows in 14051 pages for object 'TABLE_TWO'. [SQLSTATE 01000] Msg 8990, Sev 16: CHECKDB found 0 allocation errors and 166 consistency errors in table 'TABLE_TWO' (object ID 853578079). [SQLSTATE 01000]"
I've seen a lot of approaches to this, but I wanted to check here first before trying anything. Unfortunately, I just arrived and looking over the database, realized their backups have been failing for quite sometime. The application using the database is running fine so far, but backing it up freezes. I run DBCC CHECKDB (MyDB) WITH NO_INFOMSGS, ALL_ERRORMSGS and get:
Msg 8909, Level 16, State 1, Line 1 Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 844424953200640 (type Unknown), page ID (1:26483) contains an incorrect page ID in its page header. The PageId in the page header = (0:0). Msg 8909, Level 16, State 1, Line 1 Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 2533274801405952 (type Unknown), page ID (1:24589) contains an incorrect page ID in its page header. The PageId in the page header = (0:-1694496760). Msg 8909, Level 16, State 1, Line 1 Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 2533274811367424 (type Unknown), page ID (1:24301) contains an incorrect page ID in its page header. The PageId in the page header = (0:-1694496747). Msg 8909, Level 16, State 1, Line 1 Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 2533279088508928 (type Unknown), page ID (1:24334) contains an incorrect page ID in its page header. The PageId in the page header = (0:0). CHECKDB found 0 allocation errors and 4 consistency errors not associated with any single object. Msg 8928, Level 16, State 1, Line 1 Object ID 1093578934, index ID 1, partition ID 353143765729280, alloc unit ID 71668789018624 (type In-row data): Page (1:22417) could not be processed. See other errors for details. Msg 8978, Level 16, State 1, Line 1 Table error: Object ID 1093578934, index ID 1, partition ID 353143765729280, alloc unit ID 71668789018624 (type In-row data). Page (1:521649) is missing a reference from previous page (1:22417). Possible chain linkage problem. Msg 8939, Level 16, State 98, Line 1 Table error: Object ID 1093578934, index ID 1, partition ID 353143765729280, alloc unit ID 353143765729280 (type In-row data), page (1:22417). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 29362185 and -1. Msg 8976, Level 16, State 1, Line 1 Table error: Object ID 1093578934, index ID 1, partition ID 353143765729280, alloc unit ID 353143765729280 (type In-row data). Page (1:22417) was not seen in the scan although its parent (1:479) and previous (1:715544) refer to it. Check any previous errors. CHECKDB found 0 allocation errors and 4 consistency errors in table 'WebGroups' (object ID 1093578934). Msg 8929, Level 16, State 1, Line 1 Object ID 1993058136, index ID 1, partition ID 412092034711552, alloc unit ID 130617058000896 (type In-row data): Errors found in off-row data with ID 1606680576 owned by data record identified by RID = (1:1620895:7) Msg 8929, Level 16, State 1, Line 1 Object ID 1993058136, index ID 1, partition ID 412092034711552, alloc unit ID 130617058000896 (type In-row data): Errors found in off-row data with ID 1606811648 owned by data record identified by RID = (1:1620895:8) Msg 8929, Level 16, State 1, Line 1 Object ID 1993058136, index ID 1, partition ID 412092034711552, alloc unit ID 130617058000896 (type In-row data): Errors found in off-row data with ID 1607073792 owned by data record identified by RID = (1:1620895:10) Msg 8929, Level 16, State 1, Line 1 Object ID 1993058136, index ID 1, partition ID 412092034711552, alloc unit ID 130617058000896 (type In-row data): Errors found in off-row data with ID 1607204864 owned by data record identified by RID = (1:1620895:11) Msg 8929, Level 16, State 1, Line 1 Object ID 1993058136, index ID 1, partition ID 412092034711552, alloc unit ID 130617058000896 (type In-row data): Errors found in off-row data with ID 1608908800 owned by data record identified by RID = (1:1620896:10) Msg 8928, Level 16, State 1, Line 1 Object ID 1993058136, index ID 1, partition ID 412092034711552, alloc unit ID 71906736119218176 (type LOB data): Page (1:24301) could not be processed. See other errors for details. Msg 8965, Level 16, State 1, Line 1 Table error: Object ID 1993058136, index ID 1, partition ID 412092034711552, alloc unit ID 71906736119218176 (type LOB data). The off-row data node at page (1:24301), slot 0, text ID 1606680576 is referenced by page (1:24298), slot 0, but was not seen in the scan. Msg 8928, Level 16, State 1, Line 1 Object ID 1993058136, index ID 1, partition ID 412092034711552, alloc unit ID 71906736119218176 (type LOB data): Page (1:24334) could not be processed. See other errors for details. Msg 8965, Level 16, State 1, Line 1 Table error: Object ID 1993058136, index ID 1, partition ID 412092034711552, alloc unit ID 71906736119218176 (type LOB data). The off-row data node at page (1:24334), slot 0, text ID 1606811648 is referenced by page (1:24331), slot 0, but was not seen in the scan. Msg 8928, Level 16, State 1, Line 1 Object ID 1993058136, index ID 1, partition ID 412092034711552, alloc unit ID 71906736119218176 (type LOB data): Page (1:24462) could not be processed. See other errors for details. Msg 8939, Level 16, State 98, Line 1 Table error: Object ID 1993058136, index ID 1, partition ID 412092034711552, alloc unit ID 71906736119218176 (type LOB data), page (1:24462). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 29362185 and -1. Msg 8965, Level 16, State 1, Line 1 Table error: Object ID 1993058136, index ID 1, partition ID 412092034711552, alloc unit ID 71906736119218176 (type LOB data). The off-row data node at page (1:24462), slot 0, text ID 1607073792 is referenced by page (1:24429), slot 0, but was not seen in the scan. Msg 8928, Level 16, State 1, Line 1 Object ID 1993058136, index ID 1, partition ID 412092034711552, alloc unit ID 71906736119218176 (type LOB data): Page (1:24589) could not be processed. See other errors for details. Msg 8965, Level 16, State 1, Line 1 Table error: Object ID 1993058136, index ID 1, partition ID 412092034711552, alloc unit ID 71906736119218176 (type LOB data). The off-row data node at page (1:24589), slot 0, text ID 1607204864 is referenced by page (1:24591), slot 0, but was not seen in the scan. Msg 8928, Level 16, State 1, Line 1 Object ID 1993058136, index ID 1, partition ID 412092034711552, alloc unit ID 71906736119218176 (type LOB data): Page (1:26483) could not be processed. See other errors for details. Msg 8965, Level 16, State 1, Line 1 Table error: Object ID 1993058136, index ID 1, partition ID 412092034711552, alloc unit ID 71906736119218176 (type LOB data). The off-row data node at page (1:26483), slot 0, text ID 1608908800 is referenced by page (1:26470), slot 0, but was not seen in the scan. CHECKDB found 0 allocation errors and 16 consistency errors in table 'Docs' (object ID 1993058136). CHECKDB found 0 allocation errors and 24 consistency errors in database 'MyDB'. repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (MyDB).
