Recently we had issue of nonclustered index corruption on table. We faced this issue multiple times and we also run DBCC CHECKTABLE to get the details of index corruption. Every time we drop index and recreate index , but immediately it got corrupted. Our system was running fine since last 3 yrs and suddenly we got this issue.
Due to this our all update and insert started to fail, and since its main summary table we are helpless till we resolve this issue.
We checked errorlog also to get issue.
Below is image of the error.
I will also describe the scenario of our system
Ours is very large system where tables have data in billions.
The table in which we were facing issue had more than 1.5 Billion records.
We had very large volume of data and this summary table is updated every 5 minutes.
Also this table is queried entire day.
So we need nolock or readuncommitted hint to query table as this table is regularly updated every 5 minutes.
While searching on net and checking hotfix we got solution for this issue.
Its a bug in MS and has affected MSSQL 2008 to 2014 version,
The reason is also same scenario which we are using.
"This issue occurs because the NOLOCK hint causes the query to incorrectly read values in the table when the query reads the same values multiple times."
It was creating wrong update plan and ultimately corrupting non clustered index.
Here is link of hotfix for this issue.
http://support.microsoft.com/kb/2878968
After applying hotfix the issue resolved successfully.
Hope this will help others also who might face this issue.
Due to this our all update and insert started to fail, and since its main summary table we are helpless till we resolve this issue.
We checked errorlog also to get issue.
Below is image of the error.
I will also describe the scenario of our system
Ours is very large system where tables have data in billions.
The table in which we were facing issue had more than 1.5 Billion records.
We had very large volume of data and this summary table is updated every 5 minutes.
Also this table is queried entire day.
So we need nolock or readuncommitted hint to query table as this table is regularly updated every 5 minutes.
While searching on net and checking hotfix we got solution for this issue.
Its a bug in MS and has affected MSSQL 2008 to 2014 version,
The reason is also same scenario which we are using.
"This issue occurs because the NOLOCK hint causes the query to incorrectly read values in the table when the query reads the same values multiple times."
It was creating wrong update plan and ultimately corrupting non clustered index.
Here is link of hotfix for this issue.
http://support.microsoft.com/kb/2878968
After applying hotfix the issue resolved successfully.
Hope this will help others also who might face this issue.
No comments:
Post a Comment