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 |
jamiegirl764
Starting Member
30 Posts |
Posted - 2008-10-30 : 20:17:55
|
I've been reading through all the threads related to my issue and have tried all the recommended fixes. I get the following error when running a nightly maintenance plan. Database Projects: Check Data and Index Linkage...[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 8928: [Microsoft][ODBC SQL Server Driver][SQL Server]Object ID 493244812, index ID 255: Page (1:2785) could not be processed. See other errors for details.[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID 951962224, index ID 255, page ID (1:2785). The PageId in the page header = (0:8).[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 1 consistency errors in table 'Maintenance_Log' (object ID 493244812).[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 1 consistency errors in table '(Object ID 951962224)' (object ID 951962224).[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 2 consistency errors in database 'Projects'.[Microsoft][ODBC SQL Server Driver][SQL Server]repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (Projects ). The following errors were found:[Microsoft][ODBC SQL Server Driver][SQL Server]Object ID 493244812, index ID 255: Page (1:2785) could not be processed. See other errors for details.[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID 951962224, index ID 255, page ID (1:2785). The PageId in the page header = (0:8).[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 1 consistency errors in table 'Maintenance_Log' (object ID 493244812).[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 1 consistency errors in table '(Object ID 951962224)' (object ID 951962224).[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 2 consistency errors in database 'Projects'.[Microsoft][ODBC SQL Server Driver][SQL Server]repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (Projects ). ** Execution Time: 0 hrs, 0 mins, 2 secs ** I ran DBCC ('db_name', repair_allow_data_loss) with no luck, but as I'm not a DBA I didn't output it's results anywhere. I also had the server admin check the logs and hardware; no hardware failure. The data is being backed up to another drive (E:) on the local machine. The main drive (C:) had "write-caching" turned on, but not on the E: drive. We are running the following:SQL Server Dev Edition 8.00.2050 (SP4)Windows Server 2000Any advice would be greatly appreciated!Jamie |
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2008-10-31 : 02:07:58
|
Run the following and post the results please:DBCC CHECKDB (yourdbname) WITH ALL_ERRORMSGS, NO_INFOMSGSThanksPaul S. Randal, SQL Server MVPAuthor of SQL 2005 DBCC CHECKDB/repair codeAuthor & Instructor of Microsoft Certified Master - Database courseManaging Director, SQLskills.com (www.SQLskills.com/blogs/paul) |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2008-10-31 : 05:03:12
|
Do you have a backup that you can restore?--Gail ShawSQL Server MVP |
|
|
jamiegirl764
Starting Member
30 Posts |
Posted - 2008-10-31 : 13:20:34
|
Here are the results:Msg 8929, Level 16, State 1, Line 1Object ID 493244812: Errors found in text ID 20851851264 owned by data record identified by RID = (1:12752:32) Rec_ID = 47317.Msg 8929, Level 16, State 1, Line 1Object ID 493244812: Errors found in text ID 20851916800 owned by data record identified by RID = (1:12752:32) Rec_ID = 47317.Msg 8929, Level 16, State 1, Line 1Object ID 493244812: Errors found in text ID 20851982336 owned by data record identified by RID = (1:12752:32) Rec_ID = 47317.Msg 8929, Level 16, State 1, Line 1Object ID 493244812: Errors found in text ID 20852047872 owned by data record identified by RID = (1:12752:33) Rec_ID = 47318.Msg 8929, Level 16, State 1, Line 1Object ID 493244812: Errors found in text ID 20852113408 owned by data record identified by RID = (1:12752:33) Rec_ID = 47318.Msg 8929, Level 16, State 1, Line 1Object ID 493244812: Errors found in text ID 20852178944 owned by data record identified by RID = (1:12752:33) Rec_ID = 47318.Msg 8929, Level 16, State 1, Line 1Object ID 493244812: Errors found in text ID 20852703232 owned by data record identified by RID = (1:12752:34) Rec_ID = 47319.Msg 8929, Level 16, State 1, Line 1Object ID 493244812: Errors found in text ID 20852768768 owned by data record identified by RID = (1:12752:34) Rec_ID = 47319.Msg 8929, Level 16, State 1, Line 1Object ID 493244812: Errors found in text ID 20852834304 owned by data record identified by RID = (1:12752:34) Rec_ID = 47319.Msg 8929, Level 16, State 1, Line 1Object ID 493244812: Errors found in text ID 20852899840 owned by data record identified by RID = (1:12752:35) Rec_ID = 47320.Msg 8929, Level 16, State 1, Line 1Object ID 493244812: Errors found in text ID 20852965376 owned by data record identified by RID = (1:12752:35) Rec_ID = 47320.Msg 8929, Level 16, State 1, Line 1Object ID 493244812: Errors found in text ID 20853030912 owned by data record identified by RID = (1:12752:35) Rec_ID = 47320.Msg 8929, Level 16, State 1, Line 1Object ID 493244812: Errors found in text ID 20853096448 owned by data record identified by RID = (1:12752:36) Rec_ID = 47321.Msg 8929, Level 16, State 1, Line 1Object ID 493244812: Errors found in text ID 20853161984 owned by data record identified by RID = (1:12752:36) Rec_ID = 47321.Msg 8929, Level 16, State 1, Line 1Object ID 493244812: Errors found in text ID 20853227520 owned by data record identified by RID = (1:12752:36) Rec_ID = 47321.Msg 8929, Level 16, State 1, Line 1Object ID 493244812: Errors found in text ID 20853686272 owned by data record identified by RID = (1:12752:37) Rec_ID = 47322.Msg 8929, Level 16, State 1, Line 1Object ID 493244812: Errors found in text ID 20853751808 owned by data record identified by RID = (1:12752:37) Rec_ID = 47322.Msg 8929, Level 16, State 1, Line 1Object ID 493244812: Errors found in text ID 20853817344 owned by data record identified by RID = (1:12752:37) Rec_ID = 47322.Msg 8929, Level 16, State 1, Line 1Object ID 493244812: Errors found in text ID 20853882880 owned by data record identified by RID = (1:12752:38) Rec_ID = 47323.Msg 8929, Level 16, State 1, Line 1Object ID 493244812: Errors found in text ID 20853948416 owned by data record identified by RID = (1:12752:38) Rec_ID = 47323.Msg 8929, Level 16, State 1, Line 1Object ID 493244812: Errors found in text ID 20854013952 owned by data record identified by RID = (1:12752:38) Rec_ID = 47323.Msg 8929, Level 16, State 1, Line 1Object ID 493244812: Errors found in text ID 20854079488 owned by data record identified by RID = (1:12752:39) Rec_ID = 47324.Msg 8929, Level 16, State 1, Line 1Object ID 493244812: Errors found in text ID 20854145024 owned by data record identified by RID = (1:12752:40) Rec_ID = 47325.Msg 8929, Level 16, State 1, Line 1Object ID 493244812: Errors found in text ID 20854210560 owned by data record identified by RID = (1:12752:40) Rec_ID = 47325.Msg 8929, Level 16, State 1, Line 1Object ID 493244812: Errors found in text ID 20854276096 owned by data record identified by RID = (1:12752:40) Rec_ID = 47325.Msg 8929, Level 16, State 1, Line 1Object ID 493244812: Errors found in text ID 20854341632 owned by data record identified by RID = (1:12752:41) Rec_ID = 47326.Msg 8929, Level 16, State 1, Line 1Object ID 493244812: Errors found in text ID 20854407168 owned by data record identified by RID = (1:12752:41) Rec_ID = 47326.Msg 8929, Level 16, State 1, Line 1Object ID 493244812: Errors found in text ID 20854472704 owned by data record identified by RID = (1:12752:41) Rec_ID = 47326.Msg 8929, Level 16, State 1, Line 1Object ID 493244812: Errors found in text ID 20854538240 owned by data record identified by RID = (1:12752:42) Rec_ID = 47327.Msg 8929, Level 16, State 1, Line 1Object ID 493244812: Errors found in text ID 20854603776 owned by data record identified by RID = (1:12752:42) Rec_ID = 47327.Msg 8929, Level 16, State 1, Line 1Object ID 493244812: Errors found in text ID 20854669312 owned by data record identified by RID = (1:12752:42) Rec_ID = 47327.Msg 8929, Level 16, State 1, Line 1Object ID 493244812: Errors found in text ID 20854734848 owned by data record identified by RID = (1:12752:43) Rec_ID = 47328.Msg 8928, Level 16, State 1, Line 1Object ID 493244812, index ID 255: Page (1:2785) could not be processed. See other errors for details.Msg 8965, Level 16, State 1, Line 1Table error: Object ID 493244812. The text, ntext, or image node at page (1:2785), slot 1, text ID 20851851264 is referenced by page (1:12752), slot 32, but was not seen in the scan.Msg 8965, Level 16, State 1, Line 1Table error: Object ID 493244812. The text, ntext, or image node at page (1:2785), slot 3, text ID 20851916800 is referenced by page (1:12752), slot 32, but was not seen in the scan.Msg 8965, Level 16, State 1, Line 1Table error: Object ID 493244812. The text, ntext, or image node at page (1:2785), slot 5, text ID 20851982336 is referenced by page (1:12752), slot 32, but was not seen in the scan.Msg 8965, Level 16, State 1, Line 1Table error: Object ID 493244812. The text, ntext, or image node at page (1:2785), slot 7, text ID 20852047872 is referenced by page (1:12752), slot 33, but was not seen in the scan.Msg 8965, Level 16, State 1, Line 1Table error: Object ID 493244812. The text, ntext, or image node at page (1:2785), slot 8, text ID 20852113408 is referenced by page (1:12752), slot 33, but was not seen in the scan.Msg 8965, Level 16, State 1, Line 1Table error: Object ID 493244812. The text, ntext, or image node at page (1:2785), slot 9, text ID 20852178944 is referenced by page (1:12752), slot 33, but was not seen in the scan.Msg 8965, Level 16, State 1, Line 1Table error: Object ID 493244812. The text, ntext, or image node at page (1:2785), slot 11, text ID 20852703232 is referenced by page (1:12752), slot 34, but was not seen in the scan.Msg 8965, Level 16, State 1, Line 1Table error: Object ID 493244812. The text, ntext, or image node at page (1:2785), slot 13, text ID 20852768768 is referenced by page (1:12752), slot 34, but was not seen in the scan.Msg 8965, Level 16, State 1, Line 1Table error: Object ID 493244812. The text, ntext, or image node at page (1:2785), slot 14, text ID 20852834304 is referenced by page (1:12752), slot 34, but was not seen in the scan.Msg 8965, Level 16, State 1, Line 1Table error: Object ID 493244812. The text, ntext, or image node at page (1:2785), slot 16, text ID 20852899840 is referenced by page (1:12752), slot 35, but was not seen in the scan.Msg 8965, Level 16, State 1, Line 1Table error: Object ID 493244812. The text, ntext, or image node at page (1:2785), slot 18, text ID 20852965376 is referenced by page (1:12752), slot 35, but was not seen in the scan.Msg 8965, Level 16, State 1, Line 1Table error: Object ID 493244812. The text, ntext, or image node at page (1:2785), slot 20, text ID 20853030912 is referenced by page (1:12752), slot 35, but was not seen in the scan.Msg 8965, Level 16, State 1, Line 1Table error: Object ID 493244812. The text, ntext, or image node at page (1:2785), slot 22, text ID 20853096448 is referenced by page (1:12752), slot 36, but was not seen in the scan.Msg 8965, Level 16, State 1, Line 1Table error: Object ID 493244812. The text, ntext, or image node at page (1:2785), slot 23, text ID 20853161984 is referenced by page (1:12752), slot 36, but was not seen in the scan.Msg 8965, Level 16, State 1, Line 1Table error: Object ID 493244812. The text, ntext, or image node at page (1:2785), slot 24, text ID 20853227520 is referenced by page (1:12752), slot 36, but was not seen in the scan.Msg 8965, Level 16, State 1, Line 1Table error: Object ID 493244812. The text, ntext, or image node at page (1:2785), slot 26, text ID 20853686272 is referenced by page (1:12752), slot 37, but was not seen in the scan.Msg 8965, Level 16, State 1, Line 1Table error: Object ID 493244812. The text, ntext, or image node at page (1:2785), slot 28, text ID 20853751808 is referenced by page (1:12752), slot 37, but was not seen in the scan.Msg 8965, Level 16, State 1, Line 1Table error: Object ID 493244812. The text, ntext, or image node at page (1:2785), slot 30, text ID 20853817344 is referenced by page (1:12752), slot 37, but was not seen in the scan.Msg 8965, Level 16, State 1, Line 1Table error: Object ID 493244812. The text, ntext, or image node at page (1:2785), slot 32, text ID 20853882880 is referenced by page (1:12752), slot 38, but was not seen in the scan.Msg 8965, Level 16, State 1, Line 1Table error: Object ID 493244812. The text, ntext, or image node at page (1:2785), slot 34, text ID 20853948416 is referenced by page (1:12752), slot 38, but was not seen in the scan.Msg 8965, Level 16, State 1, Line 1Table error: Object ID 493244812. The text, ntext, or image node at page (1:2785), slot 35, text ID 20854013952 is referenced by page (1:12752), slot 38, but was not seen in the scan.Msg 8965, Level 16, State 1, Line 1Table error: Object ID 493244812. The text, ntext, or image node at page (1:2785), slot 37, text ID 20854079488 is referenced by page (1:12752), slot 39, but was not seen in the scan.Msg 8965, Level 16, State 1, Line 1Table error: Object ID 493244812. The text, ntext, or image node at page (1:2785), slot 39, text ID 20854145024 is referenced by page (1:12752), slot 40, but was not seen in the scan.Msg 8965, Level 16, State 1, Line 1Table error: Object ID 493244812. The text, ntext, or image node at page (1:2785), slot 40, text ID 20854210560 is referenced by page (1:12752), slot 40, but was not seen in the scan.Msg 8965, Level 16, State 1, Line 1Table error: Object ID 493244812. The text, ntext, or image node at page (1:2785), slot 41, text ID 20854276096 is referenced by page (1:12752), slot 40, but was not seen in the scan.Msg 8965, Level 16, State 1, Line 1Table error: Object ID 493244812. The text, ntext, or image node at page (1:2785), slot 43, text ID 20854341632 is referenced by page (1:12752), slot 41, but was not seen in the scan.Msg 8965, Level 16, State 1, Line 1Table error: Object ID 493244812. The text, ntext, or image node at page (1:2785), slot 45, text ID 20854407168 is referenced by page (1:12752), slot 41, but was not seen in the scan.Msg 8965, Level 16, State 1, Line 1Table error: Object ID 493244812. The text, ntext, or image node at page (1:2785), slot 46, text ID 20854472704 is referenced by page (1:12752), slot 41, but was not seen in the scan.Msg 8965, Level 16, State 1, Line 1Table error: Object ID 493244812. The text, ntext, or image node at page (1:2785), slot 48, text ID 20854538240 is referenced by page (1:12752), slot 42, but was not seen in the scan.Msg 8965, Level 16, State 1, Line 1Table error: Object ID 493244812. The text, ntext, or image node at page (1:2785), slot 50, text ID 20854603776 is referenced by page (1:12752), slot 42, but was not seen in the scan.Msg 8965, Level 16, State 1, Line 1Table error: Object ID 493244812. The text, ntext, or image node at page (1:2785), slot 52, text ID 20854669312 is referenced by page (1:12752), slot 42, but was not seen in the scan.Msg 8965, Level 16, State 1, Line 1Table error: Object ID 493244812. The text, ntext, or image node at page (1:2785), slot 53, text ID 20854734848 is referenced by page (1:2786), slot 0, but was not seen in the scan.CHECKDB found 0 allocation errors and 65 consistency errors in table 'Maintenance_Log' (object ID 493244812).Msg 8909, Level 16, State 1, Line 1Table error: Object ID 951962224, index ID 255, page ID (1:2785). The PageId in the page header = (0:8).CHECKDB found 0 allocation errors and 1 consistency errors in table '(Object ID 951962224)' (object ID 951962224).CHECKDB found 0 allocation errors and 66 consistency errors in database 'Projects'.repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (Projects ).We have backups but we only implemented this maintenance a couple months ago. The server admin also implemented another DBCC check in his backups too, so his backups are unsuccessful due to these errors.Thanks for your help!Jay |
|
|
jamiegirl764
Starting Member
30 Posts |
Posted - 2008-10-31 : 13:23:30
|
I'm sorry, I meant to say that we don't know if the backups are good because we only implemented this maintenance check a couple months ago. |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2008-10-31 : 15:14:45
|
Can you restore your latest backup onto a secondary (test) server and run checks on that. See if it's corrupt. What's the other DBCC check that the DBA has in his backups?If you go into the database "Projects" and run the following, how critical or important is the table that it returns? How critical is the table 'Maintenance_Log'?SELECT object_name(951962224)--Gail ShawSQL Server MVP |
|
|
jamiegirl764
Starting Member
30 Posts |
Posted - 2008-10-31 : 15:29:34
|
Thanks Gail, I'm one step ahead of you. I've already restored the latest backup to a different database. Or did you mean to restore to a completely separate server running MSSQL?It's not the DBA that runs a DBCC it's the backup of the server data created by the admin, which contains a DBCC check. I'm not sure how this backup is executed, but I know it contains more that just database files.NULL was returned from the "SELECT object_name(951962224)" The Maintenance_Log table is a critical table that contains crucial data. |
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2008-11-01 : 02:13:48
|
ok - if you run repair on this databse its going to delete rows from the Maintenance_Log table where the column Rec_ID is between 47317 and 47328 inclusive. You can restore from a backup, or run repair at this point. If you're going to run repair, you should save off those rows that will be deleted.This is all caused by corruption in page (1:2785) - most likely caused by your I/O subsystem (i.e. the hardware), even if there is nothing in the log this is the cause in 99.99% of the thousands of cases I've seen.ThanksPaul S. Randal, SQL Server MVPAuthor of SQL 2005 DBCC CHECKDB/repair codeAuthor & Instructor of Microsoft Certified Master - Database courseManaging Director, SQLskills.com (www.SQLskills.com/blogs/paul) |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2008-11-01 : 09:50:04
|
quote: Originally posted by jamiegirl764 Thanks Gail, I'm one step ahead of you. I've already restored the latest backup to a different database. Or did you mean to restore to a completely separate server running MSSQL?
Either works. If you run checkDB on that, is it also corrupt?Maybe I'm been paranoid, but can you also run DBCC CHECKCATALOG on the database that's corrupt?--Gail ShawSQL Server MVP |
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2008-11-01 : 10:03:09
|
quote: Originally posted by GilaMonsterMaybe I'm been paranoid, but can you also run DBCC CHECKCATALOG on the database that's corrupt?
Gail - DBCC CHECKDB includes all of DBCC CHECKCATALOG in SS2005 onwards.Paul S. Randal, SQL Server MVPAuthor of SQL 2005 DBCC CHECKDB/repair codeAuthor & Instructor of Microsoft Certified Master - Database courseManaging Director, SQLskills.com (www.SQLskills.com/blogs/paul) |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2008-11-01 : 15:26:21
|
quote: Originally posted by paulrandal Gail - DBCC CHECKDB includes all of DBCC CHECKCATALOG in SS2005 onwards.
I know, but the OP's on SQL 2000.quote: SQL Server Dev Edition 8.00.2050 (SP4)
I'm concerned about the entry in the checkDB that readsquote: CHECKDB found 0 allocation errors and 1 consistency errors in table '(Object ID 951962224)' (object ID 951962224).
and the fact that objectname for that ID came back null. Why would checkDB report an error on a table but not be able to find the name?--Gail ShawSQL Server MVP |
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2008-11-02 : 00:41:29
|
Oops - so she is - sorry.Sometimes it just didn't lookup the object name correctly in 2000 - 2005 is way more robust that 2000 in that respect.Paul S. Randal, SQL Server MVPAuthor of SQL 2005 DBCC CHECKDB/repair codeAuthor & Instructor of Microsoft Certified Master - Database courseManaging Director, SQLskills.com (www.SQLskills.com/blogs/paul) |
|
|
jamiegirl764
Starting Member
30 Posts |
Posted - 2008-11-03 : 16:19:20
|
Yes, the temp copy of the database also has error. Ran DBCC CHECKCATALOG and got this message:DBCC results for 'current database'.DBCC execution completed. If DBCC printed error messages, contact your system administrator.Paul, forgive me as I'm a newbie, by "saving off those rows" you mean to run a select statement and save the rows? I ran a select statement against that table (maintenance_log) for those rows and got the error:Msg 823, Level 24, State 2, Line 1I/O error (bad page ID) detected during read at offset 0x000000015c2000 in file 'e:\MSSQL\data\tmp_Projects.mdf'. Two weeks ago, I attempted to run a repair against the affected database and I got an error (which was fixed by restarting the server). I first set the database to single_user then ran "DBCC CHECKDB (projects, REPAIR_ALLOW_DATA_LOSS)" and return the database to multi_user and got the error:Microsoft (R) SQLMaint Utility (Unicode), Version [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 4064: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open user default database. Login failed.As for restoring from a backup, I'm not sure if that would work. This maintenance check started a several weeks ago and we don't know how long the database has had corrupted data.Thanks for all your help and being patient with me! |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2008-11-04 : 20:43:40
|
quote: Originally posted by jamiegirl764Paul, forgive me as I'm a newbie, by "saving off those rows" you mean to run a select statement and save the rows? I ran a select statement against that table (maintenance_log) for those rows and got the error:
Try doing a select of all columns except the text column. If all the damage is to the LOB pages, which it looks like, you may be able to retrieve the rest of the columns. Note, may. It's not a certainty.--Gail ShawSQL Server MVP |
|
|
jamiegirl764
Starting Member
30 Posts |
Posted - 2008-11-05 : 13:30:39
|
Yes I was able to retrieve the other columns. Thanks!I ran a ALLOW_REPAIR_DATA_LOSS on the temporary database copy last night. Looks like it's working today. I'm not sure what happened last time, but the next day the errors came back. Will let you know how it goes tomorrow. Thanks for your help!Jay |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2008-11-05 : 14:06:18
|
If the errors are reoccurring, then you've either hit a bug in SQL, or your IO system is causing problems. Have a look at the windows event log and any hardware logs and see if you can see anything amiss.--Gail ShawSQL Server MVP |
|
|
|
|
|
|
|