April 17, 2018 at 3:18 pm
I have a corrupt database. The last log backup_finish_date is 2018-04-17 14:42:27.000 (backup_start_date is 2018-04-17 14:42:25.000) . I have restored this database backup chain to another server and it DBCC's ok with no errors.
However DBCC was running about the same time and in the SQL Server error log I see:
Date 4/17/2018 2:32:13 PM / Log SQL Server (Current - 4/17/2018 2:18:00 PM) / Message * DBCC database corruption
I know I can restore from backup but my question is how can a backup that finished after the corruption was found be good?
April 17, 2018 at 3:41 pm
What are the exact errors do you get from CHECKDB?
April 17, 2018 at 3:47 pm
We have had the same corruption now 6 times in two weeks with the same database. Each time I restore from a database backup taken before the corruption was detected. This time I accidentally restored to a time later than the corruption was detected. I first restore the database backup to a different database name and DBCC that new restored database and then if the DBCC is good, which it has always been, I drop the temporary database and perform the "real" restore and then DBCC the restored database.
Anyway the corruption is as follows, 8909, 8665 followed by a few 8928 or 9
/* Date and time: 2018-04-17 14:28:49
Command: */
SET LOCK_TIMEOUT 5000; DBCC CHECKDB ([Tfs_Development]) WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITY
Msg 8909, Level 16, State 1, Server TX01SQLP01\OPSP01, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (1:185607) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
Msg 8965, Level 16, State 1, Server TX01SQLP01\OPSP01, Line 1
Table error: Object ID 424270654, index ID 1, partition ID 72057602017853440, alloc unit ID 72057602286092288 (type LOB data). The off-row data node at page (1:185607), slot 0, text ID 286261248 is referenced by page (1:1618643), slot 0, but was not seen in the scan.
Msg 8928, Level 16, State 1, Server TX01SQLP01\OPSP01, Line 1
Object ID 424270654, index ID 1, partition ID 72057602017853440, alloc unit ID 72057602286092288 (type LOB data): Page (1:185607) could not be processed. See other errors for details.
Msg 8929, Level 16, State 1, Server TX01SQLP01\OPSP01, Line 1
Object ID 424270654, index ID 1, partition ID 72057602017853440, alloc unit ID 72057602286223360 (type In-row data): Errors found in off-row data with ID 286261248 owned by data record identified by RID = (1:20866:81)
Msg 8929, Level 16, State 1, Server TX01SQLP01\OPSP01, Line 1
Object ID 424270654, index ID 1, partition ID 72057602017853440, alloc unit ID 72057602286223360 (type In-row data): Errors found in off-row data with ID 286261248 owned by data record identified by RID = (1:7906063:60)
Msg 8929, Level 16, State 1, Server TX01SQLP01\OPSP01, Line 1
Object ID 424270654, index ID 1, partition ID 72057602017853440, alloc unit ID 72057602286223360 (type In-row data): Errors found in off-row data with ID 286261248 owned by data record identified by RID = (1:7910549:3)
Msg 8929, Level 16, State 1, Server TX01SQLP01\OPSP01, Line 1
Object ID 424270654, index ID 1, partition ID 72057602017853440, alloc unit ID 72057602286223360 (type In-row data): Errors found in off-row data with ID 286261248 owned by data record identified by RID = (1:7914754:1)
Msg 8929, Level 16, State 1, Server TX01SQLP01\OPSP01, Line 1
Object ID 424270654, index ID 1, partition ID 72057602017853440, alloc unit ID 72057602286223360 (type In-row data): Errors found in off-row data with ID 286261248 owned by data record identified by RID = (1:7933494:1)
Msg 8929, Level 16, State 1, Server TX01SQLP01\OPSP01, Line 1
Object ID 424270654, index ID 1, partition ID 72057602017853440, alloc unit ID 72057602286223360 (type In-row data): Errors found in off-row data with ID 286261248 owned by data record identified by RID = (1:7936540:6)
Msg 8929, Level 16, State 1, Server TX01SQLP01\OPSP01, Line 1
Object ID 424270654, index ID 1, partition ID 72057602017853440, alloc unit ID 72057602286223360 (type In-row data): Errors found in off-row data with ID 286261248 owned by data record identified by RID = (1:7951584:4)
Msg 8929, Level 16, State 1, Server TX01SQLP01\OPSP01, Line 1
Object ID 424270654, index ID 1, partition ID 72057602017853440, alloc unit ID 72057602286223360 (type In-row data): Errors found in off-row data with ID 286261248 owned by data record identified by RID = (1:7953442:23)
Msg 8929, Level 16, State 1, Server TX01SQLP01\OPSP01, Line 1
Object ID 424270654, index ID 1, partition ID 72057602017853440, alloc unit ID 72057602286223360 (type In-row data): Errors found in off-row data with ID 286261248 owned by data record identified by RID = (1:7953540:12)
CHECKDB found 0 allocation errors and 1 consistency errors not associated with any single object.
CHECKDB found 0 allocation errors and 11 consistency errors in table 'tbl_Content' (object ID 424270654).
CHECKDB found 0 allocation errors and 12 consistency errors in database 'Tfs_Development'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (Tfs_Development).
Outcome: Failed
Duration: 00:03:51
Date and time: 2018-04-17 14:32:40
April 17, 2018 at 4:54 pm
You're getting transient bad page reads from your I/O subsystem - that explains the zero'd page header and the 'weird' behavior you're seeing. Time to get I/O subsystem diagnostics running...
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
April 18, 2018 at 1:08 pm
Thanks Paul, EMC says it must be Microsoft and Microsoft says it must be EMC, I will go back to EMC and see if there is more that they can check.
Thanks again!
Brett
April 18, 2018 at 1:27 pm
No problem. Look in your error log to see if there are any message 825s - that proves that it's EMC. See https://www.sqlskills.com/blogs/paul/a-little-known-sign-of-impending-doom-error-825/
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply