July 27, 2008 at 9:11 pm
Hi all,
I am facing Indexed view consistency error in one of my database which is in sql server 2005. Error log of this consistency is.
DBCC results for 'vwSPReport'.
Msg 8907, Level 16, State 1, Line 1
Indexed view 'vwSPReport' (object ID 699201591) contains rows not produced by the view definition. Refer to Books Online for more information on this error. This does not necessarily represent an integrity issue with the data in this database.
Msg 8908, Level 16, State 1, Line 1
Indexed view 'vwSPReport' (object ID 699201591) does not contain all rows that the view definition produces. Refer to Books Online for more information on this error. This does not necessarily represent an integrity issue with the data in this database.
There are 3981961 rows in 147481 pages for object "vwSPReport".
CHECKTABLE found 0 allocation errors and 2 consistency errors in table 'vwSPReport' (object ID 699201591).
repair_rebuild is the minimum repair level for the errors found by DBCC CHECKTABLE (MYDB.dbo.vwSPReport).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
----------------------What i did for workaround of this error--------------
1. I tried dbcc checktable(vwspreprot, repair_allow_data_loss) option in single user mode.. but that won't help in my case
2. Then i tried dbcc checktable(vwspreport, repair_rebuild) option in single user mode.. but that also won't help in my case
---------------------------------------------------------------------------------------
How to solve this consitency problem ? Is there any other workaround of this problem or it is a bug...?
Regards
Shashi kant Chauhan
July 28, 2008 at 12:50 am
Hi check this
DBCC You can use CHECKDB and DBCC CHECKTABLE to see if an indexed view contains the same rows that are found by computing the view from the base tables. If DBCC gives errors 8907 or 8908 that indicate that the stored view is not identical to the computed view, consider the following to resolve the issue.
Does your view definition contain hints?
In SQL Server 2000, you can create indexed views that contain table hints such as NOLOCK. In some situations, this can lead to indexed view corruption. If your view contains table hints, drop the view, edit its definition to remove such hints, and re-create it . Then, re-create the index on the view.
Does the indexed view compute a SUM aggregate over values of type float or real?
If so, are the only differences between the indexed view and computed view in the aggregated column, and are the differences small for corresponding stored and computed rows? If they are not significant, considering your data and application, no corrective action is required.
If the differences are significant, drop the index on the view and re-create it. The corruption in this case is probably because of the approximate nature of floating-point arithmetic. The order in which numbers are added during indexed view maintenance can sometimes have a generally small affect on the final result. For more information about how to use approximate data types, see Using decimal, float, and real Data. If your application uses floating-point types, but your requirements can be met using an exact decimal type (numeric, money, or decimal), consider using numeric instead in a revised version of your indexed view.
If the indexed view does not contain an aggregate over values of type float or real and you receive errors 8907 or 8708, drop the index on the view and re-create it.
Do not use ALTER INDEX REBUILD to try to remove the differences between the stored and the computed view, because ALTER INDEX REBUILD does not recalculate the view before rebuilding the index.
After recreating the index on the view, run DBCC CHECKTABLE on the view to verify that no differences remain. If differences do remain, consider hardware or other issues as a possible cause.
For more information check :ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/92940f58-431d-429a-b20c-db574ba8dff3.htm
SQL BOL
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply