Wednesday, 22 February 2017

DBCC CHECKDB failed due to invalid object id

This anomaly persist due to deleting objects that are not removed from system tables properly due to some reasons which is in fact ignored in legacy SQL version (2000) however catch up by DBCC in later versions (Most probably after migration or upgrade).

Error Description:

Error 3854: [Microsoft][ODBC SQL Server Driver][SQL Server]Attribute (parent_object_id=1977058079) of row (object_id=1993058136) in sys.objects has a matching row (object_id=1977058079) in sys.objects (type=S ) that is invalid


1) Ignore the errors, though database consistency check will not able to complete.

2) Export the data to new database using import/export utility.

3) Remove the anomaly from system base table, as system table (sysschobjs) is not directly accessible so DAC access is required, steps as below.

- Enable configuration (allow update and DAC)
- Restart SQL in single user mode with –m
- As both objects (1977058079, 1993058136) are in same table along with syskeys table so delete the child record 1993058136.
- delete  FROM sys.sysschobjs WHERE ID=1993058136;
- remove –m parameter and restart SQL in normal model
- disable allow update and DAC

No comments:

Post a Comment