July 8, 2020 at 2:24 pm
I ran a DBCC on a database and it found some anomalies. Just looking for some insight as to how to interpret this output (the part of the output that was in red), what do you think? Do these indexes found below need to be rebuilt or reorganized, or perhaps I need to rerun DBCC CHECKDB with a parameter so it can fix the issue(?).
This below OUTPUT was in red;
Msg 8951, Level 16, State 1, Line 1
Table error: table 'AuditLog' (ID 1010102639). Data row does not have a matching index row in the index 'PK_AuditLog' (ID 2). Possible missing or invalid keys for the index row matching:
Msg 8955, Level 16, State 1, Line 1
Data row (1:1655767:62) identified by (TableID = 11 and UNIQUIFIER = 88835) with index values 'AuditLogID = 1954599 and TableID = 11 and UNIQUIFIER = 88835'.
Msg 8951, Level 16, State 1, Line 1
Table error: table 'AuditLog' (ID 1010102639). Data row does not have a matching index row in the index 'IX_AuditLog_DateTime' (ID 3). Possible missing or invalid keys for the index row matching:
Msg 8955, Level 16, State 1, Line 1
Data row (1:1655767:62) identified by (TableID = 11 and UNIQUIFIER = 88835) with index values 'PostedDateTime = '2009-11-07 09:43:40.560' and TableID = 11 and UNIQUIFIER = 88835'.
July 8, 2020 at 3:55 pm
First, I'm not an expert here and conservative. When I have doubts, I open a CSS case with MS to work through issues. I'd also immediately restore the oldest backup I have and run DBCC CHECKDB there. Use TABLERESULTS so you can sort through output.
If these are non clustered indexes, you can do a drop and recreate of the index.
If these are the clustered index, you may have data loss. Working through that is beyond the scope of a post here.
Note that corruption is almost always some hardware issue. Cabling, drivers, firmware, etc. You need to check through things and verify that you don't have issues in any other databases on this hardware. Paul better explains things and has more info on his blog.
Ref: https://www.sqlskills.com/blogs/paul/teched-demo-nonclustered-index-corruption/
July 8, 2020 at 4:43 pm
Thanks Steve, I will read that article. I have also found some blogs that also suggest that it is a drop and recreate so that the index will not "rebuild" with the same corruption. I have restored the DB onto a test server to experiment there (the restore did move over the corruption). I'm not sure about the hardware corruption because it is a VM on VMWare and is on a SAN (still can be corrupt on the SAN, of course, but the VM moves around at times to different hardware).
July 8, 2020 at 4:45 pm
It's often a SAN driver/hardware/etc that can cause this, but it could be memory module issues, so movement of the VM an be a pain to diagnose. Get the SAN people to double things things with the vendor and verify versions of everything. Corruption is nothing to fool with.
Corruption moves through backups, which is why I'd start digging into old backups and find out when it happened.
July 8, 2020 at 6:24 pm
One of the indexes is the primary key - but that index may not be the clustered index. If it isn't the clustered index you may be able to rebuild it to remove the corruption.
As a backup - you need to identify the latest backup that does not have this corruption and locate all transaction log backups from that point forward. If you don't have those backups and the index rebuilds don't fix the problem then your only option will be one that includes some data loss.
Also - you need to identify when this occurred. Review the SQL logs and server logs to see if you have any indication of storage related issues. If you do not address this issue at the server/storage level it is going to happen again - and when it happens again there is no guarantee that it will only affect non-clustered indexes.
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
July 8, 2020 at 6:49 pm
Thanks for the insight Jeffrey. I did try a rebuild and ran DBCC again and it reveals the same error. Luckily, this table (and database) can afford to lose some data. This is the first DBCC in 3 years on this inherited database, so I'm pretty sure we don't have a good backup before this happened. I am using this issue to practice for when data loss does matter. Am I correct in concluding that the only option (backups aside) is to drop and recreate the index (primary key, non-clustered), and that this will cause the index to be built from the table, skipping over the corruption? Is that what you mean by "includes data loss"?
If the index is corrupt and not the table, wouldn't this mean no data loss? Or, if the table is corrupted, doesn't that mean the data is already lost anyway (again, assuming no backup)? Just trying to get a deeper understanding of the "data loss" part.
(I do understand this could be an indication of a broader issue in the future other than this table/index - thanks for confirming).
July 8, 2020 at 7:15 pm
Since the clustered index is the data - any other indexes should be able to be dropped and recreated with no data loss. The non-clustered indexes will be built from the data in the clustered index or from the heap (if no clustered index exists).
If the corruption occurs in the clustered index - since that is the data then you cannot rebuild it without suffering data loss.
You stated you tried to rebuild the index for the PK and it still shows as corrupted. That is a concern - we would need to see how you rebuilt that index. Since it is the primary key - you should remove the constraint, drop the index and then recreate it.
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
July 9, 2020 at 3:22 pm
I dropped the index (and primary key constraint). When I try to re-add the primary key, I received an error that there are duplicate values. I did indeed find duplicate values in the column. I went to remove this value (there is only one duplicate) so I can add the PK, but I get this error;
Msg 8630, Level 16, State 1, Line 3
Internal Query Processor Error: The query processor encountered an unexpected error during execution (HRESULT = 0x80040e19).
I've read that there might be a FK relationship with another table (not that I can see, and if there is no longer a PK, then there can't be an FK in another table(?))
I've also read that it could be an SQL patch that is needed on this server (the patches are up to date as of a month ago).
How can I delete the 2 rows that are causing a duplicate from this table and get around this error?
July 9, 2020 at 3:43 pm
Open a case with MS or call a consultant like sqlSkills. I've had similar issues, and what really happens is you have broken pages that the query engine cannot read and interpret. They can often help you recover some of the data, or remove those pages. Likely you need to move all data you can to a new table and drop this one to remove the corruption.
July 9, 2020 at 3:50 pm
Glad to know it wasn't something I was overlooking. I like the idea of moving this data to a new table, and then back again to one with the same name. If all else fails I can restore to get back where I started with the corruption, but the PK in place. Thanks for the feedback!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply