I recently takeover many databases that are in SQL 2005 environment but they were originally in 2000. The compatibility mode is still 80 and nobody ever bothered to run DBCC ever on the system (even after the upgrade). When I did integrity check this past weekend and I found two kinds of error. The first one is:
And several errors like this:
So I run DBCC UPDATEUSAGE and the errors of the second type goes away. But the first error still persists and when I run integrity check again I get the same message:
Msg 8992, Level 16, State 1, Line 2 Check Catalog Msg 3853, State 1: Attribute (referenced_major_id=194099732,referenced_minor_id=4) of row (class=0,object_id=304056169,column_id=0,referenced_major_id=194099732,referenced_minor_id=4) in sys.sql_dependencies does not have a matching row (object_id=194099732,column_id=4) in sys.columns.
CHECKDB found 0 allocation errors and 1 consistency errors not associated with any single object. CHECKDB found 0 allocation errors and 1 consistency errors in database 'MyDB'.
I was thinking to restore a backup but i figured it might have the same problem since it was not checked for errors before.
I have found this article by Paul Randal so far and it seems complicated with a lot of warnings and it involves shutting down the server. So I have to read it again carefully and maybe test it in test environment first.
My question is How can I fix this? Is there any chance that I might lose data if i repaired it with ALLOW data loss. Do I have to worry about this? How can I tell when the database is corrupted? What is the consequence of these corruption.(so far no body complained)
If the info I provided here is not complete please let me know and I will try to add some more info.
[EDIT]: I have found this article from SQL SERVER Central but it doesnt talk about Msg 8992, Level 16, State 1, Line 2.
If you run repair with Allow data loss - you best expect to lose data. I would reread he article by Paul and reread as often as necessary. You can find out the database has corruption by running checkdb just as you did. I would worry about the corruption - and try to fix it.
answered Apr 12, 2010 at 11:51 AM
The errors you are showing are not data corruption.
One is saying that
The other error is just saying 'well I lost count of the reserved page count for this particular index', which SQL Server 2000 did all the time. If no-one had run
It would be worth finding out the name of object 304056169, then using
i.e. Something along the lines of:
I would follow the recommendation by Paul Randal.
Just because no-one has yet complained, you shouldn't put your head in the sand. You've seen the corruption, now you must fix it.
If you can reproduce the error in a test environment, then go for it - that way you'll become more confident that Paul's process will fix your problem. Looking at the errors you have and the way Paul explained the cause, it should be reproducible.
answered Apr 12, 2010 at 12:06 PM
Kev Riley ♦♦