March 27, 2017 at 9:30 am
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?
March 27, 2017 at 10:40 am
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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
March 27, 2017 at 11:25 am
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'
March 27, 2017 at 11:37 am
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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
March 31, 2017 at 6:09 am
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.
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