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

Question

question

DaniSQL asked Apr 12, '10

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

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.

sql-server-2005 dbcc corruption

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield ♦♦ commented · Apr 12, 2010 at 12:27 PM

+1 for the good amount of detail

0 ·

Answer 1 · 2010-04-12T11:51:05Z

CirqueDeSQLeil answered Apr 12, '10

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.

6

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

DaniSQL commented · Apr 12, 2010 at 11:53 AM

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

0 ·

DaniSQL commented · Apr 12, 2010 at 11:56 AM

Do I have to take a measure asap?

0 ·

CirqueDeSQLeil commented · Apr 12, 2010 at 12:51 PM

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.

0 ·

DaniSQL commented · Apr 12, 2010 at 01:24 PM

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?

0 ·

CirqueDeSQLeil commented · Apr 12, 2010 at 03:40 PM

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.

0 ·

Show more comments

Answer 2 · 2010-04-12T12:06:28Z

Kev Riley answered Apr 12, '10

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.

3

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

DaniSQL commented · Apr 12, 2010 at 02:40 PM

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

0 ·

DaniSQL commented · Apr 14, 2010 at 05:59 PM

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

0 ·

Kev Riley ♦♦ commented · Apr 15, 2010 at 09:04 AM

DaniSQL : Matt has it covered!

0 ·

Answer 3 · 2010-04-12T12:07:51Z

Matt Whitfield answered Apr 12, '10

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)

8

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield ♦♦ commented · Apr 14, 2010 at 06:16 PM

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...

2 ·

Matt Whitfield ♦♦ commented · Apr 12, 2010 at 01:34 PM

@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! :)

1 ·

DaniSQL commented · Apr 12, 2010 at 01:26 PM

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?

0 ·

DaniSQL commented · Apr 12, 2010 at 01:36 PM

Thanks Matt :-)

0 ·

Matt Whitfield ♦♦ commented · Apr 12, 2010 at 01:39 PM

No worries, let us know how it works out!

0 ·

Show more comments

question