sql metadata inconsistencies

Question

sql metadata inconsistencies

Steve Smith-163358

SSCommitted

Points: 1750
More actions
March 27, 2017 at 9:30 am

#319923

Hi,
Running DBCC CHECKDB on one of our client's databases reports 2 consistency errors.
These errors are related to the metadata for an old function that no longer exists, or has since been recreated.
Msg 8992, Level 16, State 1, Line 1 Check Catalog Msg 3853, State 1: Attribute (object_id=375997462) of row (object_id=375997462,parameter_id=0) in sys.parameters does not have a matching row (object_id=375997462) in sys.objects. Msg 8992, Level 16, State 1, Line 1 Check Catalog Msg 3853, State 1: Attribute (object_id=375997462) of row (object_id=375997462,parameter_id=1) in sys.parameters does not have a matching row (object_id=375997462) in sys.objects.
According the the client, these inconsistencies have been occurring for years, and they just ignore them.
i'm aware that the best approach for resolving these issues is to export all the data and reimport it, and that it's possible to update the system tables to tidy up the orphaned data left behind..... although the latter method is unsupported.
However, the client is reluctant to make any changes because everything has been working fine even with these 2 errors.
So, my question is, what's the downside of just ignoring these corruptions.
They relate to an object that no longer exists and isn't used, so is there really a problem with just ignoring the errors in DBCC?
April 2, 2017 at 11:06 pm

This was removed by the editor as SPAM

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 101098 More actions · Answer 1

I'd certainly try to fix it without allowing SQL to lose any data. If it can fix it cleanly, why leave it bad?

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Steve Smith-163358 SSCommitted Points: 1750 More actions · Answer 2

I'm aware of 2 options for fixing the problem.

1) The Supported Method: export all data, create new database, reimport all data
2) The Quicker (hopefully) Method: using DAC, directly edit the system tables

The client is reluctant to do either method given that they're not aware of having any problems in the last 5+ years that they've had the errors.
I don't like the idea of leaving the corruption there, however, am struggling to come up with a better argument than 'because it shouldn't be there'

ScottPletcher SSC Guru Points: 101098 More actions · Answer 3

USE database_name;
ALTER DATABASE database_name SET SINGLE_USER;
DBCC CHECKDB ( database_name, REPAIR_REBUILD );
ALTER DATABASE database_name SET MULTI_USER;

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Steve Smith-163358 SSCommitted Points: 1750 More actions · Answer 4

sorry.
i missed your last post.

The DBCC repair_rebuild didn't help this at all.
From what I've read online, this can only be resolved using the 2 methods I mentioned earlier.

However, the client is still unwilling to 'risk' their system by fixing it given that they don't think they will have any problems with this corruption.