Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-07-06 : 07:42:11
|
| Vimal Kanth writes "I am using SQL Server 2000 and one of my databases has loads of consistency errors. When i try to query a particular record from a table it displays an error message similar to thisCould not find the index entry for RID '168927001232500300' in index page (1:95510), index ID 0, database 'Tristar'.Then, i executed the DBCC CHECKDB command after going through some documentation. The results of which were something like thisTable error: Database 'Tristar', index 'DMS_VOUCHER.index_1739153241' (ID 1739153241) (index ID 2). Extra or invalid key for the keys:Index row (1:11229:144) with values (ROWGUID = 22814645-5B2B-4B4E-A1D8-9B49DF7C8CEF`¢0; and DEALER_ID = 10121 and BRANCH_ID = 1.242260474E-307 and VOUCHER_ID = 9507) points to the data row identified by ().Then, i executed the DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS command. I lost around 3000 records when i excuted thisI dont want any data loss as this is very critical.Can you please help me overcome this problem? Thanks in advance.RegardsVimal Kanth" |
|
|
franco
Constraint Violating Yak Guru
255 Posts |
Posted - 2005-07-06 : 08:38:06
|
| There are 2 other commands that you can use and they don't destroy anything.REPAIR_FAST:Performs minor, nontime-consuming repair actions such as repairing extra keys in nonclustered indexes. These repairs can be done quickly and without risk of data loss.andREPAIR_REBUILD:Performs all repairs done by REPAIR_FAST and includes time-consuming repairs such as rebuilding indexes. These repairs can be done without risk of data loss.HTH.Franco |
 |
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2005-07-06 : 16:46:00
|
| The repair levels apart from repair_allow_data_loss may not repair all the errors you have. repair_allow_data_loss was deliberately named so that its obvious that you may lose data by using it.You must have had additional errors to the example you gave as that one would not cause any rows to be lost - only the nonclustered index to be repaired.When you get corruption errors the very best way to recover is to restore from your up-to-date backups. You should also work out, if possible, why the errors occured. Check the Windows event logs and the SQL Server errorlog for hardware errors.If you have no choice but to run repair_allow_data_loss (CHECKDB tells you which option you must use to repair all problems), you can run it inside a user transaction so that you can see how much data is lost and rollback the repair if you want.Additionally, consider manually extracting data from the affected tables before running repair to see how much data you can salvage. Remember that repair_allow_data_loss exists to put the database into a structurally consistent state and as such it may have to remove data. Also, be aware that it does not maintain constraints, so any inherent business logic you have built into your database may be broken after running repair.If you post the full output of checkdb I can tell you exactly what your problem is (I wrote it) and why you lost data.Regards.Paul RandalDev Lead, Microsoft SQL Server Storage Engine |
|
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2005-07-06 : 16:48:16
|
| BTW, that offer extends to anyone - post CHECKDB results and I'll tell you what's wrong and what repair will do.Also be aware that I wrote detailed explanations of all errors that CHECKDB can return and these are included in the latest BOL rev for SQL Server 2000 (and will be in BOL for SQL Server 2005 too)ThanksPaul RandalDev Lead, Microsoft SQL Server Storage Engine |
|
|
|
Bob7
Starting Member
5 Posts |
Posted - 2005-07-19 : 10:46:36
|
| Paul, Here is my output from DBCC CHECKFILEGROUP. Note that I renamed a table [TBL_ClientDeleteArchive TORN PAGE], where most of the errors occur. Other errors occur in sysobjects (yikes!).Errors began to occur about ten days ago. DBCC DBREINDEX reduces the number of errors, but some remain. Our busiest season is upon us, this is an urgent worry. Your assistance is appreciated.BobBob |
|
|
|
Bob7
Starting Member
5 Posts |
Posted - 2005-07-19 : 10:47:37
|
| OOPSHere it is:Job 'Nightly Integrity Checks' : Step 2, 'Physical' : Began Executing 2005-07-19 01:13:17Msg 8909, Sev 16: Table error: Object ID 0, index ID 0, page ID (4:188001). The PageId in the page header = (0:0). [SQLSTATE 42000]Msg 8909, Sev 16: Table error: Object ID 1, index ID 128, page ID (4:188011). The PageId in the page header = (38541:3210926). [SQLSTATE 42000]Msg 8909, Sev 16: Table error: Object ID 1, index ID 128, page ID (4:188015). The PageId in the page header = (38541:3210954). [SQLSTATE 42000]Msg 8909, Sev 16: Table error: Object ID 6417, index ID 16448, page ID (4:188013). The PageId in the page header = (0:109687420). [SQLSTATE 42000]Msg 8909, Sev 16: Table error: Object ID 65537, index ID 0, page ID (4:187968). The PageId in the page header = (6177:136116). [SQLSTATE 42000]Msg 8909, Sev 16: Table error: Object ID 65537, index ID 0, page ID (4:187984). The PageId in the page header = (6417:136116). [SQLSTATE 42000]Msg 8909, Sev 16: Table error: Object ID 65537, index ID 0, page ID (4:188000). The PageId in the page header = (6177:136116). [SQLSTATE 42000]Msg 8909, Sev 16: Table error: Object ID 136116, index ID 1673, page ID (4:188012). The PageId in the page header = (1028:65884). [SQLSTATE 42000]Msg 8909, Sev 16: Table error: Object ID 109687388, index ID 64, page ID (4:188010). The PageId in the page header = (13:105144). [SQLSTATE 42000]Msg 2536, Sev 16: DBCC results for 'TWICES_APP'. [SQLSTATE 01000]Msg 8954, Sev 16: CHECKFILEGROUP found 0 allocation errors and 1 consistency errors not associated with any single object. [SQLSTATE 01000]Msg 8990, Sev 16: CHECKFILEGROUP found 0 allocation errors and 2 consistency errors in table 'sysobjects' (object ID 1). [SQLSTATE 01000]Msg 8990, Sev 16: CHECKFILEGROUP found 0 allocation errors and 1 consistency errors in table '(Object ID 6417)' (object ID 6417). [SQLSTATE 01000]Msg 8990, Sev 16: CHECKFILEGROUP found 0 allocation errors and 3 consistency errors in table '(Object ID 65537)' (object ID 65537). [SQLSTATE 01000]Msg 8990, Sev 16: CHECKFILEGROUP found 0 allocation errors and 1 consistency errors in table '(Object ID 136116)' (object ID 136116). [SQLSTATE 01000]Msg 2536, Sev 16: DBCC results for 'TBL_ImmTrac_Svc'. [SQLSTATE 01000]Msg 2593, Sev 16: There are 0 rows in 2 pages for object 'TBL_ImmTrac_Svc'. [SQLSTATE 01000]Msg 2536, Sev 16: DBCC results for 'TBL_ImmTrac_Hist'. [SQLSTATE 01000]Msg 2593, Sev 16: There are 0 rows in 3 pages for object 'TBL_ImmTrac_Hist'. [SQLSTATE 01000]Msg 8909, Sev 16: Table error: Object ID 109687441, index ID 64, page ID (4:188014). The PageId in the page header = (1:24264). [SQLSTATE 42000]Msg 8909, Sev 16: Table error: Object ID 981578535, index ID 2, page ID (4:187960). The PageId in the page header = (4:187192). [SQLSTATE 42000]Msg 8909, Sev 16: Table error: Object ID 981578535, index ID 2, page ID (4:187976). The PageId in the page header = (4:187400). [SQLSTATE 42000]Msg 8909, Sev 16: Table error: Object ID 981578535, index ID 2, page ID (4:187992). The PageId in the page header = (4:187192). [SQLSTATE 42000]Msg 8990, Sev 16: CHECKFILEGROUP found 0 allocation errors and 1 consistency errors in table '(Object ID 109687388)' (object ID 109687388). [SQLSTATE 01000]Msg 8990, Sev 16: CHECKFILEGROUP found 0 allocation errors and 1 consistency errors in table '(Object ID 109687441)' (object ID 109687441). [SQLSTATE 01000] <snip>Msg 2536, Sev 16: DBCC results for 'TBL_IvstImmVisit'. [SQLSTATE 01000]Msg 8909, Sev 16: Table error: Object ID 981578535, index ID 2, page ID (4:188002). The PageId in the page header = (4:187330). [SQLSTATE 42000]Msg 8909, Sev 16: Table error: Object ID 981578535, index ID 2, page ID (4:188003). The PageId in the page header = (4:187395). [SQLSTATE 42000]Msg 8909, Sev 16: Table error: Object ID 981578535, index ID 2, page ID (4:188004). The PageId in the page header = (4:187396). [SQLSTATE 42000]Msg 8909, Sev 16: Table error: Object ID 981578535, index ID 2, page ID (4:188005). The PageId in the page header = (4:187397). [SQLSTATE 42000]Msg 8909, Sev 16: Table error: Object ID 981578535, index ID 2, page ID (4:188006). The PageId in the page header = (4:187398). [SQLSTATE 42000]Msg 8909, Sev 16: Table error: Object ID 981578535, index ID 2, page ID (4:188007). The PageId in the page header = (4:187399). [SQLSTATE 42000]Msg 8909, Sev 16: Table error: Object ID 981578535, index ID 2, page ID (4:188008). The PageId in the page header = (4:187400). [SQLSTATE 42000]Msg 8909, Sev 16: Table error: Object ID 981578535, index ID 2, page ID (4:188009). The PageId in the page header = (4:187369). [SQLSTATE 42000]Msg 8928, Sev 16: Object ID 1091027168, index ID 0: Page (4:187960) could not be processed. See other errors for details. [SQLSTATE 42000]Msg 8928, Sev 16: Object ID 1091027168, index ID 0: Page (4:187968) could not be processed. See other errors for details. [SQLSTATE 42000]Msg 8928, Sev 16: Object ID 1091027168, index ID 0: Page (4:187976) could not be processed. See other errors for details. [SQLSTATE 42000]Msg 8928, Sev 16: Object ID 1091027168, index ID 0: Page (4:187984) could not be processed. See other errors for details. [SQLSTATE 42000]Msg 8928, Sev 16: Object ID 1091027168, index ID 0: Page (4:187992) could not be processed. See other errors for details. [SQLSTATE 42000]Msg 8928, Sev 16: Object ID 1091027168, index ID 0: Page (4:188000) could not be processed. See other errors for details. [SQLSTATE 42000]Msg 2593, Sev 16: There are 7405425 rows in 389507 pages for object 'TBL_IvstImmVisit'. [SQLSTATE 01000]Msg 8990, Sev 16: CHECKFILEGROUP found 0 allocation errors and 11 consistency errors in table 'TBL_IvstImmVisit' (object ID 981578535). [SQLSTATE 01000]Msg 2536, Sev 16: DBCC results for 'TBL_ClientDeleteArchive TORN PAGE'. [SQLSTATE 01000]Msg 8928, Sev 16: Object ID 1091027168, index ID 0: Page (4:188001) could not be processed. See other errors for details. [SQLSTATE 42000]Msg 8928, Sev 16: Object ID 1091027168, index ID 0: Page (4:188002) could not be processed. See other errors for details. [SQLSTATE 42000]Msg 8928, Sev 16: Object ID 1091027168, index ID 0: Page (4:188003) could not be processed. See other errors for details. [SQLSTATE 42000]Msg 8928, Sev 16: Object ID 1091027168, index ID 0: Page (4:188004) could not be processed. See other errors for details. [SQLSTATE 42000]Msg 8928, Sev 16: Object ID 1091027168, index ID 0: Page (4:188005) could not be processed. See other errors for details. [SQLSTATE 42000]Msg 8928, Sev 16: Object ID 1091027168, index ID 0: Page (4:188006) could not be processed. See other errors for details. [SQLSTATE 42000]Msg 8928, Sev 16: Object ID 1091027168, index ID 0: Page (4:188007) could not be processed. See other errors for details. [SQLSTATE 42000]Msg 8928, Sev 16: Object ID 1091027168, index ID 0: Page (4:188008) could not be processed. See other errors for details. [SQLSTATE 42000]Msg 8928, Sev 16: Object ID 1091027168, index ID 0: Page (4:188009) could not be processed. See other errors for details. [SQLSTATE 42000]Msg 8928, Sev 16: Object ID 1091027168, index ID 0: Page (4:188010) could not be processed. See other errors for details. [SQLSTATE 42000]Msg 8928, Sev 16: Object ID 1091027168, index ID 0: Page (4:188011) could not be processed. See other errors for details. [SQLSTATE 42000]Msg 8928, Sev 16: Object ID 1091027168, index ID 0: Page (4:188012) could not be processed. See other errors for details. [SQLSTATE 42000]Msg 8928, Sev 16: Object ID 1091027168, index ID 0: Page (4:188013) could not be processed. See other errors for details. [SQLSTATE 42000]Msg 8928, Sev 16: Object ID 1091027168, index ID 0: Page (4:188014) could not be processed. See other errors for details. [SQLSTATE 42000]Msg 8928, Sev 16: Object ID 1091027168, index ID 0: Page (4:188015) could not be processed. See other errors for details. [SQLSTATE 42000]Msg 2593, Sev 16: There are 127613 rows in 1597 pages for object 'TBL_ClientDeleteArchive TORN PAGE'. [SQLSTATE 01000]Msg 8990, Sev 16: CHECKFILEGROUP found 0 allocation errors and 21 consistency errors in table 'TBL_ClientDeleteArchive TORN PAGE' (object ID 1091027168). [SQLSTATE 01000]Msg 2536, Sev 16: DBCC results for 'TBL_UnidUniqueId'. [SQLSTATE 01000]There are 34 rows in 1 pages for object 'TBL_UnidUniqueId'. [SQLSTATE 01000] <snip>CHECKFILEGROUP found 0 allocation errors and 42 consistency errors in database 'TWICES_APP'. [SQLSTATE 01000]repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKFILEGROUP (TWICES_APP ). [SQLSTATE 01000]DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000]Bob |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-07-19 : 11:28:50
|
| >>BTW, that offer extends to anyone - post CHECKDB results and I'll tell you what's wrong and what repair will do.That is a very generous offer, Paul. Thanks for hangin' around and contributing to sqlteam.com!Be One with the OptimizerTG |
|
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2005-07-19 : 12:28:30
|
quote:
Originally posted by Bob7
OOPSHere it is:Job 'Nightly Integrity Checks' : Step 2, 'Physical' : Began Executing 2005-07-19 01:13:17Msg 8909, Sev 16: Table error: Object ID 0, index ID 0, page ID (4:188001). The PageId in the page header = (0:0). [SQLSTATE 42000]<snip>
This one's easy - you've got a bad disk or a bad controller. If the number of errors keeps growing, that's further evidence.Are there are indications of hw failures in the SQL Server errorlog or the Windows event logs?Paul RandalDev Lead, Microsoft SQL Server Storage Engine |
|
|
|
Bob7
Starting Member
5 Posts |
Posted - 2005-07-19 : 12:47:12
|
| Thanks Paul.Our hardware team says nothing appears in the Windows events log, and no hardware diagnostics.I'll drop and rebuild the indexes, ask the hardware team to double check the logs and diagnostics. Is the line: Msg 8909, Sev 16: Table error: Object ID 0, index ID 0, page ID (4:188001). The PageId in the page header = (0:0). especially significant of hardware error. If not, then how do you tell?Again, thanks.Bob |
|
|
|
Bob7
Starting Member
5 Posts |
Posted - 2005-07-19 : 12:47:17
|
| Thanks Paul.Our hardware team says nothing appears in the Windows events log, and no hardware diagnostics.I'll drop and rebuild the indexes, ask the hardware team to double check the logs and diagnostics. Is the line: Msg 8909, Sev 16: Table error: Object ID 0, index ID 0, page ID (4:188001). The PageId in the page header = (0:0). especially significant of hardware error. If not, then how do you tell?Again, thanks.Bob |
|
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2005-07-19 : 13:10:45
|
quote:
Originally posted by Bob7
Thanks Paul.Our hardware team says nothing appears in the Windows events log, and no hardware diagnostics.I'll drop and rebuild the indexes, ask the hardware team to double check the logs and diagnostics. Is the line: Msg 8909, Sev 16: Table error: Object ID 0, index ID 0, page ID (4:188001). The PageId in the page header = (0:0). especially significant of hardware error. If not, then how do you tell?
Dropping and rebuilding the indexes won't make any difference - you're just going to write the data back into the bad hardware.The errors you listed showed that the page headers were being corrupted. There aren't any known bugs I can think of that would do this, and its generally indicative of a disk or controller writing garbage to disk.Paul RandalDev Lead, Microsoft SQL Server Storage Engine |
|
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2005-07-19 : 15:20:47
|
| Have your hardware folks check on the firmware levels of raid controllers, motherboards, fibercards, etc., as well. We had a problem with outdated firmware that caused us a great deal of corruption. Sadly this was some years back, and we no longer have the DBCC outputs to confirm this is anything like your current problem. |
|
|
|
Bob7
Starting Member
5 Posts |
Posted - 2005-07-20 : 16:24:52
|
| Paul, I need some coaching to accurately assess the damage from our hardware failure. Please offer your insights. We are still running with online users (for sad lack of ready alternatives). Statewide health clinics are online and entering data in a "back to school" rush.1) BOL seems to indicate that indexes and data are evaluated for consistency. The errors I've seen so far are from DBCC CHECKFILEGROUP <index file group>. When CHECKFILEGROUP diagnoses an error, are we certain the error was on the <index file group>, or might the inconsistancy be caused by errors on <data file group> during data Vs index comparison?2) Can I run DBCC CHECKFILEGROUP while online users do data entry? We process perhaps 100,000 transactions a day. Processors typically run at 10% to 25%, with brief peaks around 60% - 80%. Disk queues are typically in single digits, and usually average approx 1 per second.I will not be doing any repairs with users online, of course, just diagnostics if this is wise?3) It seems preferable to me, to run DBCC CHECKFILEGROUP for every file group, instead of DBCC CHECKDB. Will the cummulative output be equivalent? Can I run 2 or 3 of these at once? I have 4 file groups.4) We did a manual cluster failover last night, which switched us to two different IO controlers. We rebuilt the most effected index, according to CHECKFILEGROUP diagnostics. 144 errors remained. We see a new message #8939 as well as the usual #8928. Examples below:Msg 8928, Sev 16: Object ID 1091027168, index ID 0: Page (4:187960) could not be processed. See other errors for details. [SQLSTATE 42000]Msg 8939, Sev 16: Table error: Object ID 1091027168, index ID 0, page (4:187960). Test (IS_ON (BUF_IOERR, bp->bstat) && bp->berrcode) failed. Values are 2057 and -1. [SQLSTATE 42000]Does this #8939 indicate worse corruption?5) Is it true that any data we collect at risk of loss in this condition? We run FULL recovery with LogShipping, but the weekly complete restore onto LogShipping propagates the page-ID-errors.Thanks a million. I am sure your time is precious.Bob |
|
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2005-07-21 : 11:04:35
|
quote:
Originally posted by Bob7
Paul, I need some coaching to accurately assess the damage from our hardware failure. Please offer your insights. We are still running with online users (for sad lack of ready alternatives). Statewide health clinics are online and entering data in a "back to school" rush.1) BOL seems to indicate that indexes and data are evaluated for consistency. The errors I've seen so far are from DBCC CHECKFILEGROUP <index file group>. When CHECKFILEGROUP diagnoses an error, are we certain the error was on the <index file group>, or might the inconsistancy be caused by errors on <data file group> during data Vs index comparison?
When checking a filegroup that is only used for storing indexes, all the base tables (heap/clustered index) that those indexes are on are also checked, regardless of which filegroup they're stored in. If the error is on an non-clustered index page (any index ID > 1) and that index is on the index filegroup, then the error's there. If the error is on a page with index ID <= 1, then its on the data filegroup. That's a general rule of thumb. However, there are many errors where there's a mismatch between a table and index where its difficult to say exactly where the problem is without examining the pages themselves.quote:
2) Can I run DBCC CHECKFILEGROUP while online users do data entry? We process perhaps 100,000 transactions a day. Processors typically run at 10% to 25%, with brief peaks around 60% - 80%. Disk queues are typically in single digits, and usually average approx 1 per second.I will not be doing any repairs with users online, of course, just diagnostics if this is wise?
Absolutely (on SQL Server 2000 and up). The throughput degradation you'll observe depends on your database schema and your IO systems (i.e. whether CHECKDB will be cpu- or IO-bound). You'll most likely see it being IO-bound. Be aware that to run online in SQL Server 2000, CHECKDB does manual log analysis to acheive a transactionally consistent view of the database and there are some known problems with this that could cause spurious errors under some rare circumstances - you're not hitting them it seems. These issues don't exist in SQL Server 2005.quote:
3) It seems preferable to me, to run DBCC CHECKFILEGROUP for every file group, instead of DBCC CHECKDB. Will the cummulative output be equivalent? Can I run 2 or 3 of these at once? I have 4 file groups.
No - its far better to just run CHECKDB otherwise you're going to be duplicating a lot of work. Considering what I explained in my first paragraph above, if you have three index filegroups and one data filegroup, checking each of the index filegroups will also check the data filegroup too - thus checking the data filegroup 3 times. Also, running them in parallel will cripple your system's IO throughput. CHECKDB has a sophisticated mechanism to allow it read the pages in disk order, regardless of the tables/indexes to which they belong and so its best to let it figure out the most efficient way to check the data.quote:
4) We did a manual cluster failover last night, which switched us to two different IO controlers. We rebuilt the most effected index, according to CHECKFILEGROUP diagnostics. 144 errors remained. We see a new message #8939 as well as the usual #8928. Examples below:Msg 8928, Sev 16: Object ID 1091027168, index ID 0: Page (4:187960) could not be processed. See other errors for details. [SQLSTATE 42000]Msg 8939, Sev 16: Table error: Object ID 1091027168, index ID 0, page (4:187960). Test (IS_ON (BUF_IOERR, bp->bstat) && bp->berrcode) failed. Values are 2057 and -1. [SQLSTATE 42000]Does this #8939 indicate worse corruption?
Yes. The 8939 is saying that the page had an IO error - ie the disk is bad. Its in the base table so now you're going to lose real table data - everything that was on that page has been lost. Failing over IO controllers will still point to the same disk, right? So this means its the disk(s) that are bad. I recommend moving to new disks and restoring from a backup. Running repair will cause data loss here.quote:
5) Is it true that any data we collect at risk of loss in this condition? We run FULL recovery with LogShipping, but the weekly complete restore onto LogShipping propagates the page-ID-errors.
Yes. You're going to lose data unless you have clean backups.quote:
Thanks a million. I am sure your time is precious.Bob
You're very welcome.ThanksPaul RandalDev Lead, Microsoft SQL Server Storage Engine |
|
|
|
WiL53
Starting Member
1 Post |
Posted - 2005-07-21 : 23:08:59
|
| Hi Paul, really appreciate your help. Can you please help me interpret this log. Our server experienced a hard drive failure. The drive has been replaced but checkdb shows errors. I executed teh command again with the allow data loss option and here is the output. Can you tell me if i lost data and if i still have errors?Server: Msg 8909, Level 16, State 1, Line 1Table error: Object ID 0, index ID 0, page ID (1:9373794). The PageId in the page header = (0:0).DBCC results for 'PrismSigFTS'. The error has been repaired.CHECKDB found 0 allocation errors and 1 consistency errors not associated with any single object.CHECKDB fixed 0 allocation errors and 1 consistency errors not associated with any single object.DBCC results for 'sysobjects'.There are 56 rows in 1 pages for object 'sysobjects'.DBCC results for 'sysindexes'.There are 35 rows in 3 pages for object 'sysindexes'.DBCC results for 'syscolumns'.There are 358 rows in 7 pages for object 'syscolumns'.DBCC results for 'systypes'.There are 26 rows in 1 pages for object 'systypes'.DBCC results for 'syscomments'.There are 127 rows in 16 pages for object 'syscomments'.DBCC results for 'sysfiles1'.There are 2 rows in 1 pages for object 'sysfiles1'.DBCC results for 'syspermissions'.There are 49 rows in 1 pages for object 'syspermissions'.DBCC results for 'sysusers'.There are 12 rows in 1 pages for object 'sysusers'.DBCC results for 'sysproperties'.There are 0 rows in 0 pages for object 'sysproperties'.DBCC results for 'sysdepends'.There are 309 rows in 2 pages for object 'sysdepends'.DBCC results for 'sysreferences'.There are 0 rows in 0 pages for object 'sysreferences'.DBCC results for 'sysfulltextcatalogs'.Server: Msg 8909, Level 16, State 1, Line 1Table error: Object ID 538976288, index ID 12336, page ID (1:9373793). The PageId in the page header = (155:1513750560).Server: Msg 8928, Level 16, State 1, Line 1Object ID 1977058079, index ID 0: Page (1:9373792) could not be processed. See other errors for details.Server: Msg 8939, Level 16, State 1, Line 1Table error: Object ID 1977058079, index ID 0, page (1:9373792). Test (IS_ON (BUF_IOERR, bp->bstat) && bp->berrcode) failed. Values are 2057 and -1.Server: Msg 8928, Level 16, State 1, Line 1Object ID 1977058079, index ID 0: Page (1:9373793) could not be processed. See other errors for details.Server: Msg 8928, Level 16, State 1, Line 1Object ID 1977058079, index ID 0: Page (1:9373794) could not be processed. See other errors for details.Server: Msg 8976, Level 16, State 1, Line 1Table error: Object ID 1977058079, index ID 1. Page (1:9373792) was not seen in the scan although its parent (1:7451823) and previous (1:368748) refer to it. Check any previous errors.There are 0 rows in 0 pages for object 'sysfulltextcatalogs'.DBCC results for 'sysfulltextnotify'.There are 0 rows in 0 pages for object 'sysfulltextnotify'.DBCC results for 'sysfilegroups'.There are 1 rows in 1 pages for object 'sysfilegroups'. The error has been repaired.CHECKDB found 0 allocation errors and 1 consistency errors in table '(Object ID 538976288)' (object ID 538976288).CHECKDB fixed 0 allocation errors and 1 consistency errors in table '(Object ID 538976288)' (object ID 538976288).DBCC results for 'ACHD'. The error has been repaired. The error has been repaired. The error has been repaired. The error has been repaired. The error has been repaired.Server: Msg 8980, Level 16, State 1, Line 1Table error: Object ID 1977058079, index ID 1. Index node page (1:7451823), slot 99 refers to child page (1:9373793) and previous child (1:9373792), but they were not encountered.Server: Msg 8980, Level 16, State 1, Line 1Table error: Object ID 1977058079, index ID 1. Index node page (1:7451823), slot 100 refers to child page (1:9373794) and previous child (1:9373793), but they were not encountered.Server: Msg 8978, Level 16, State 1, Line 1Table error: Object ID 1977058079, index ID 1. Page (1:9373795) is missing a reference from previous page (1:9373794). Possible chain linkage problem. The error has been repaired. The error has been repaired. The error has been repaired.Repair: Page (1:9373792) has been deallocated from object ID 1977058079, index ID 0.Repair: Page (1:9373793) has been deallocated from object ID 1977058079, index ID 0.Repair: Page (1:9373794) has been deallocated from object ID 1977058079, index ID 0.Clustered index successfully restored for object 'dbo.ACHD' in database 'PrismSigFTS'.There are 13157409 rows in 4800690 pages for object 'ACHD'.CHECKDB found 0 allocation errors and 8 consistency errors in table 'ACHD' (object ID 1977058079).CHECKDB fixed 0 allocation errors and 8 consistency errors in table 'ACHD' (object ID 1977058079).DBCC results for 'dtproperties'.There are 0 rows in 0 pages for object 'dtproperties'.CHECKDB found 0 allocation errors and 10 consistency errors in database 'PrismSigFTS'.CHECKDB fixed 0 allocation errors and 10 consistency errors in database 'PrismSigFTS'.DBCC execution completed. If DBCC printed error messages, contact your system administrator. |
|
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2005-07-23 : 11:36:39
|
| Yes - you lost 3 pages of data (the ones that repair deallocated). I don't know if you still have errors - depends if another CHECKDB comes back clean.I take it you don't have a backup and that's why you ran repair?ThanksPaul RandalDev Lead, Microsoft SQL Server Storage Engine |
|
|
|
Westley
Posting Yak Master
229 Posts |
Posted - 2005-07-24 : 22:54:52
|
| Hi Paul and others, I hope I'm not too late to ask this qns here, but I just got an error while I'm doing a dbcc command, here is the result:Server: Msg 8964, Level 16, State 1, Line 1Table error: Object ID 1627152842. The text, ntext, or image node at page (3:38397), slot 1, text ID 58534068224 is not referenced.Server: Msg 8964, Level 16, State 1, Line 1Table error: Object ID 1627152842. The text, ntext, or image node at page (3:44595), slot 15, text ID 58541211648 is not referenced.Server: Msg 8964, Level 16, State 1, Line 1Table error: Object ID 1627152842. The text, ntext, or image node at page (3:45080), slot 6, text ID 58546388992 is not referenced.Server: Msg 8964, Level 16, State 1, Line 1Table error: Object ID 1627152842. The text, ntext, or image node at page (5:120497), slot 5, text ID 58538196992 is not referenced.DBCC results for 'tbl_Instructions'.There are 38420 rows in 537 pages for object 'tbl_Instructions'.CHECKTABLE found 0 allocation errors and 4 consistency errors in table 'tbl_Instructions' (object ID 1627152842).repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKTABLE (TestDB.dbo.tbl_Instructions ).I tried to copy all the data to another table and it seems to fix it, as i run the dbcc command on the new table, no error shown. I guess my qn will be does dbcc checkdb with REPAIR_REBUILD will fix this issue? also, how does this happen in the first place? I just applied sp4 on this server yesterday, will that got to do with it?Thanks |
|
|
|
ssrikrish
Yak Posting Veteran
69 Posts |
Posted - 2005-07-24 : 23:03:48
|
| It is definitely a hardware problem. I have faced the exact issue before and guess what we did lose data and I had to restore data from a backup db that was 6 months old. The diagnostic results were not self-explanatory and even the MS support team couldn't let us know for sure if it was a hardware problem. We had to replace the drives and it solved the problem. No sleepless nights thereafter. Reindexing is not going to help. |
|
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2005-07-25 : 14:17:03
|
quote:
Originally posted by Westley
Hi Paul and others, I hope I'm not too late to ask this qns here, but I just got an error while I'm doing a dbcc command, here is the result:Server: Msg 8964, Level 16, State 1, Line 1Table error: Object ID 1627152842. The text, ntext, or image node at page (3:38397), slot 1, text ID 58534068224 is not referenced.Server: Msg 8964, Level 16, State 1, Line 1Table error: Object ID 1627152842. The text, ntext, or image node at page (3:44595), slot 15, text ID 58541211648 is not referenced.Server: Msg 8964, Level 16, State 1, Line 1Table error: Object ID 1627152842. The text, ntext, or image node at page (3:45080), slot 6, text ID 58546388992 is not referenced.Server: Msg 8964, Level 16, State 1, Line 1Table error: Object ID 1627152842. The text, ntext, or image node at page (5:120497), slot 5, text ID 58538196992 is not referenced.DBCC results for 'tbl_Instructions'.There are 38420 rows in 537 pages for object 'tbl_Instructions'.CHECKTABLE found 0 allocation errors and 4 consistency errors in table 'tbl_Instructions' (object ID 1627152842).repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKTABLE (TestDB.dbo.tbl_Instructions ).I tried to copy all the data to another table and it seems to fix it, as i run the dbcc command on the new table, no error shown. I guess my qn will be does dbcc checkdb with REPAIR_REBUILD will fix this issue? also, how does this happen in the first place? I just applied sp4 on this server yesterday, will that got to do with it?Thanks
This one's not definitely a hardware problem as there's no evidence of gross corruption on the pages. However, it may be a hardware problem in that the updates to these pages were lost. The only way to know for sure is to look through the transaction log for the updates to these pages and match against the LSNs on the pages.The four text nodes referenced have been orphaned - they don't have parent text nodes in the text index or in any data rows. This is unlikely to be an SP4 problem. Have you run a full CHECKDB to see if any other problems exist in the database?REPAIR_REBUILD won't correct this - the clue to what repair level to use is in the CHECKTABLE output:"repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKTABLE (TestDB.dbo.tbl_Instructions )."Rebuilding doesn't touch text.My advice: run a full CHECKDB and post the results.ThanksPaul RandalDev Lead, Microsoft SQL Server Storage Engine |
|
|
|
Westley
Posting Yak Master
229 Posts |
Posted - 2005-07-25 : 22:18:43
|
| Thanks Paul, I tried DBCC CheckDB on that DB, and thats seems to be the only error. Anyway, I ran DBCC with REPAIR_REBUILD and repair_allow_data_lost, both doesn't seems to fix the issue, so what I did was backup the table out and recreate that table then reload the data back in, that seems to fix it, just wondering why and whats the best way to keep it not happening again, as the only thing we change was the sp4 upgrade, and we did DBCC checks everyday, it only comes out the day after we apply sp4. By the way, is it more useful (or helpful) to use check the DB with DBCC CheckDB or check each table using DBCC CheckTable command?Thanks |
|
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2005-07-25 : 22:26:34
|
quote:
Originally posted by Westley
Thanks Paul, I tried DBCC CheckDB on that DB, and thats seems to be the only error. Anyway, I ran DBCC with REPAIR_REBUILD and repair_allow_data_lost, both doesn't seems to fix the issue, so what I did was backup the table out and recreate that table then reload the data back in, that seems to fix it, just wondering why and whats the best way to keep it not happening again, as the only thing we change was the sp4 upgrade, and we did DBCC checks everyday, it only comes out the day after we apply sp4. By the way, is it more useful (or helpful) to use check the DB with DBCC CheckDB or check each table using DBCC CheckTable command?Thanks
Can you post the output from running REPAIR_ALLOW_DATA_LOSS? I'd like to see why it says it couldn't fix it because it should have.Doing an export/drop/create/import will fix this problem as the drop will have deleted the whole table, including the orphaned text.I don't know why it happened. If you didn't crash, don't have any hardware failure evidence in the Windows evetn log or the SQL errorlog, don't show problems when running disk/controller/memory diagnostics then its not an obvious hardware issue and I don't know of any bugs. Only way to tell is to analyze the log/database as I mentioned before.Its better to run CHECKDB rather than individual CHECKTABLEs. As I mentioned further back in the thread, CHECKDB has some sophisticated algorithms that all it to read the whole database in allocation order and still check consistency of multiple tables at the same time. CHECKTABLE uses the same algorithm but only for all the indexes of a single table - so its much more efficient to run CHECKDB. That way you cut down the number of disk-head sweeps of the disks.RegardsPaul RandalDev Lead, Microsoft SQL Server Storage Engine |
|
|
|
Next Page
|
|
|
|
|
How to get rid of Consistency errors without data