November 19, 2015 at 10:44 am
We have an issue with rebuilding an index.
We've tried to drop and recreate, but no joy.
Error message is:
--
TITLE: Microsoft SQL Server Management Studio
------------------------------
Rebuild failed for Index 'PK_PreAudTranHdr'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.3000.0+((SQL11_PCU_Main).121019-1322+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Rebuild+Index&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0xce765820; actual: 0xce765828). It occurred during a read of page (1:21132) in database ID 9 at offset 0x0000000a518000 in file 'C:\Program Files\Microsoft SQL Server\MSSQL.MSSQLSERVER\MSSQL\Data\ETL.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
The statement has been terminated. (Microsoft SQL Server, Error: 824)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=824&LinkId=20476
November 19, 2015 at 11:44 am
You need to run an integrity check on that database:
DBCC CHECKDB(ETL) WITH NO_INFOMSGS, ALL_ERRORMSGS;
Review the results (and post the errors here if needed) to figure out what your next options are...
If you have a known good backup without the corruption, and transaction log backups from that point until current - you can restore from that backup and apply the transaction up to the current point in time.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 19, 2015 at 12:14 pm
OK so I ran the following.
DBCC CHECKTABLE (PreAudTranHdr, REPAIR_ALLOW_DATA_LOSS)
Here are the results.
DBCC results for '<TableName>'.
Repair: The Clustered index successfully rebuilt for the object "dbo.<TableName>" in database "ETL".
Repair: The page (1:21132) has been deallocated from object ID 831342026, index ID 1, partition ID 72057594061717504, alloc unit ID 72057594116964352 (type In-row data).
Repair: The page (1:21137) has been deallocated from object ID 831342026, index ID 1, partition ID 72057594061717504, alloc unit ID 72057594116964352 (type In-row data).
Repair: The page (1:21365) has been deallocated from object ID 831342026, index ID 1, partition ID 72057594061717504, alloc unit ID 72057594116964352 (type In-row data).
Repair: The Nonclustered index successfully rebuilt for the object "dbo.<TableName>, IX_<TableName>_FBGuid" in database "ETL".
Repair: The Nonclustered index successfully rebuilt for the object "dbo.<TableName>, IX_<TableName>_LastUpdated" in database "ETL".
Repair: The Nonclustered index successfully rebuilt for the object "dbo.<TableName>, IX_<TableName>_InvoiceDate" in database "ETL".
Msg 8945, Level 16, State 1, Line 1
Table error: Object ID 831342026, index ID 1 will be rebuilt.
The error has been repaired.
Msg 8928, Level 16, State 1, Line 1
Object ID 831342026, index ID 1, partition ID 72057594061717504, alloc unit ID 72057594116964352 (type In-row data): Page (1:21132) could not be processed. See other errors for details.
The error has been repaired.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 831342026, index ID 1, partition ID 72057594061717504, alloc unit ID 72057594116964352 (type In-row data), page (1:21132). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 2057 and -4.
The error has been repaired.
Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 831342026, index ID 1, partition ID 72057594061717504, alloc unit ID 72057594116964352 (type In-row data). Page (1:21132) was not seen in the scan although its parent (1:25670) and previous (1:21131) refer to it. Check any previous errors.
The error has been repaired.
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 831342026, index ID 1, partition ID 72057594061717504, alloc unit ID 72057594116964352 (type In-row data). Page (1:21133) is missing a reference from previous page (1:21132). Possible chain linkage problem.
The error has been repaired.
Msg 8928, Level 16, State 1, Line 1
Object ID 831342026, index ID 1, partition ID 72057594061717504, alloc unit ID 72057594116964352 (type In-row data): Page (1:21137) could not be processed. See other errors for details.
The error has been repaired.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 831342026, index ID 1, partition ID 72057594061717504, alloc unit ID 72057594116964352 (type In-row data), page (1:21137). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 2057 and -4.
The error has been repaired.
Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 831342026, index ID 1, partition ID 72057594061717504, alloc unit ID 72057594116964352 (type In-row data). Page (1:21137) was not seen in the scan although its parent (1:25670) and previous (1:21136) refer to it. Check any previous errors.
The error has been repaired.
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 831342026, index ID 1, partition ID 72057594061717504, alloc unit ID 72057594116964352 (type In-row data). Page (1:21138) is missing a reference from previous page (1:21137). Possible chain linkage problem.
The error has been repaired.
Msg 8928, Level 16, State 1, Line 1
Object ID 831342026, index ID 1, partition ID 72057594061717504, alloc unit ID 72057594116964352 (type In-row data): Page (1:21365) could not be processed. See other errors for details.
The error has been repaired.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 831342026, index ID 1, partition ID 72057594061717504, alloc unit ID 72057594116964352 (type In-row data), page (1:21365). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 2057 and -4.
The error has been repaired.
Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 831342026, index ID 1, partition ID 72057594061717504, alloc unit ID 72057594116964352 (type In-row data). Page (1:21365) was not seen in the scan although its parent (1:29815) and previous (1:21364) refer to it. Check any previous errors.
The error has been repaired.
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 831342026, index ID 1, partition ID 72057594061717504, alloc unit ID 72057594116964352 (type In-row data). Page (1:21366) is missing a reference from previous page (1:21365). Possible chain linkage problem.
The error has been repaired.
Msg 8945, Level 16, State 1, Line 1
Table error: Object ID 831342026, index ID 7 will be rebuilt.
The error has been repaired.
Msg 8945, Level 16, State 1, Line 1
Table error: Object ID 831342026, index ID 8 will be rebuilt.
The error has been repaired.
Msg 8945, Level 16, State 1, Line 1
Table error: Object ID 831342026, index ID 9 will be rebuilt.
The error has been repaired.
There are 620985 rows in 93941 pages for object "<TableName>".
CHECKTABLE found 0 allocation errors and 12 consistency errors in table '<TableName>' (object ID 831342026).
CHECKTABLE fixed 0 allocation errors and 12 consistency errors in table '<TableName>' (object ID 831342026).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
November 19, 2015 at 12:20 pm
The following command fixed the issue
DBCC CHECKTABLE (PreAudTranHdr, REPAIR_ALLOW_DATA_LOSS)
November 19, 2015 at 12:30 pm
How much data did it throw away in the process?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 19, 2015 at 12:34 pm
I actually posted the results earlier. Or at least I thought I posted them earlier with the DBCC Solution.
I no longer have those results.
November 19, 2015 at 12:42 pm
Not asking for the output of CheckDB. I'm asking if you know or checked how many rows were discarded when you ran DBCC CHECKTABLE (PreAudTranHdr, REPAIR_ALLOW_DATA_LOSS), because it will have lost data.
How does that lost data affect the business? Has it resulted in orphaned data (especially since the name suggests this is a header table)?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 19, 2015 at 12:46 pm
This is part of an ETL process which we are now refreshing.
Once we are at a point to view reports, I can report those details.
Fortunately for us, this was on a staging server.
Thanks
Dwayne
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply