December 1, 2015 at 12:58 pm
When I exec the following:
DBCC CHECKDB (DBNAME) WITH NO_INFOMSGS;
I am receiving numerous errors similar to the ones that follow.
Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3853, State 1: Attribute (object_id=12331604) of row (object_id=12331604,parameter_id=1) in sys.parameters does not have a matching row (object_id=12331604) in sys.objects.
I have determined that there are numerous records in the sys.parameters view which do not have a corresponding record in the sysobjects view.
select * from sys.parameters param WHERE not exists (select 1 from sys.objects obj where obj.object_id = param.object_id) -. This returns almost 1000 records.
I am guessing at this point. I believe there were a number of records deleted from system table in a previous version of SQL which was possible but not recommended and the parameters were not deleted.
Is there a way to clean these up with out rebuilding a database?
December 1, 2015 at 1:30 pm
What object has an ID of 12331604?
And check all the other IDs being reported from CheckDB too.
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
December 1, 2015 at 1:38 pm
Thanks for the response - There does not appear to any objectid that matches this number in the sysobjects view. It appears to be an orphaned record in the sys.parameters view.
December 1, 2015 at 2:04 pm
Ah, I thought it was the other way around. Ignore them (they're harmless except for causing checkDB to fail) or rebuild the DB (script, export, recreate).
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
December 1, 2015 at 2:10 pm
Thank you!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply