CHECKDB found 0 allocation errors and 1 consistency errors not associated with any single object.

Question

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:

Msg 8992, Level 16, State 1, Server SERVER-A, Line 1 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.

And several errors like this:

Msg 2508, Level 16, State 3, Server SERVER-A, Line 1 The In-row data RSVD page count for object "OBJ-1", index ID 0, partition ID 20288381976576, alloc unit ID 20288381976576 (type In-row data) is incorrect. Run DBCC UPDATEUSAGE. CHECKDB found 0 allocation errors and 1 consistency errors in table 'OBJ-1' (object ID 309576141).

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)

Please help.

If the info I provided here is not complete please let me know and I will try to add some more info.

Thanks,

[EDIT]: I have found this article from SQL SERVER Central but it doesnt talk about Msg 8992, Level 16, State 1, Line 2.

Answer 1

0

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 '10 at 11:51 AM

CirqueDeSQLeil
3.9k ● 6 ● 11 ● 15

What if I left it alone until I figured a solution? Nobody complained so far.

Apr 12 '10 at 11:53 AM DaniSQL

Do I have to take a measure asap?

Apr 12 '10 at 11:56 AM DaniSQL

I would take action as soon as possible. The problem with the corruption is that it will persist in your backups. Should you need to restore your database, you will restore corruption. Thus an additional measure to perform is to get a good clean backup of your database as soon as the corruption is fixed.

Apr 12 '10 at 12:51 PM CirqueDeSQLeil

I agree and I will definitely try to fix it. What I was trying to say was can I take my time to figure out a solution or do I have to take swift measure asap?

Apr 12 '10 at 01:24 PM DaniSQL

Most certainly take your time to troubleshoot and take the appropriate action. Swift action can cause more harm than good. Certainly fix it, but certainly make sure you are taking your time with it and taking the correct course of action.

Apr 12 '10 at 03:40 PM CirqueDeSQLeil

(comments are locked)

Answer 2

0

The errors you are showing are not data corruption.

One is saying that sys.sql_dependencies is showing a link from an object to an object that doesn't exist. This doesn't surprise me - sys.sql_dependencies is hideously broken, and there are many many ways to confuse it, just like sysdepends in 2000.

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 UPDATEUSAGE for a while, then it's pretty likely those inconsistencies have been there since you upgraded from compatibility level 80. Ask yourself - why on earth would there need to be a DBCC UPDATEUSAGE command anyway?

It would be worth finding out the name of object 304056169, then using sp_refreshsqlmodule on that object (assuming that it is a procedure / view). That will probably clean up sys.sql_dependencies.

i.e. Something along the lines of:

DECLARE @sql varchar(MAX)
SET @sql = 'sp_refreshsqlmodule ''' + OBJECT_NAME(304056169) + ''''
EXEC (@sql)

answered Apr 12 '10 at 12:07 PM

Matt Whitfield ♦♦
29.2k ● 56 ● 63 ● 87

Matt, the object is a user created stored procedure. I tried EXEC sys.sp_refreshsqlmodule 'dbo.MyStoredProcedure' and it is throwing the error " Msg 213, Level 16, State 1, Procedure MyStoredProcedure, Line 33 Insert Error: Column name or number of supplied values does not match table definition. " Is these the correct implementation? Would recreating the stored procedure help? Any Suggestions?

Apr 12 '10 at 01:26 PM DaniSQL

@DaniSQL - It seems like that stored procedure is an old one, seeing as if it can't recompile it with refreshsqlmodule, then it won't even run. I would re-create the stored procedure (with the code changes necessary to make it work), but ONLY AFTER finding out if any code uses it. You might have an app sitting in the background hammering this proc and getting errors - and making it work correctly could have unexpected results. Welcome to the world of inheriting databases! :)

Apr 12 '10 at 01:34 PM Matt Whitfield ♦♦

Thanks Matt :-)

Apr 12 '10 at 01:36 PM DaniSQL

No worries, let us know how it works out!

Apr 12 '10 at 01:39 PM Matt Whitfield ♦♦

If all the errors were updateusage based, then you don't need to be too worried about them. It would be worth your while investigating running updateusage regularly as part of your index maintenance. However, one thing to note is that sys.sql_dependencies is deprecated in 2008 in favour of sys.sql_expression_dependencies, so it may be that your dependency error wouldn't show up. It's worth investigating that. And don't worry about the incorrect file name, that's just poor previous maintenance...

Apr 14 '10 at 06:16 PM Matt Whitfield ♦♦

(comments are locked)

Answer 3

0

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 '10 at 12:06 PM

Kev Riley ♦♦
46.1k ● 38 ● 43 ● 69

Thanks Kev. I will fix the problem (I will try it first on test server)

Apr 12 '10 at 02:40 PM DaniSQL

Kev: Please see my new comment under Matts Response. Thanks.

Apr 14 '10 at 05:59 PM DaniSQL

DaniSQL : Matt has it covered!

Apr 15 '10 at 09:04 AM Kev Riley ♦♦

(comments are locked)

CHECKDB found 0 allocation errors and 1 consistency errors not associated with any single object.

Follow this question

Related Questions