Consistency Errors - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Data Corruption Issues

Consistency Errors

Author

Topic

drewsalem
Constraint Violating Yak Guru

304 Posts

Posted - 2005-11-22 : 07:08:28

Hi all, long time reader, first time poster, budding dba passionate about SQL Server.

I received the following Consistency errors:

4] Database IMM481_Nidderdale_Visitor: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 8970: [Microsoft][ODBC SQL Server Driver][SQL Server]Row error: Object ID 573245097, index ID 0, page ID (1:170), row ID 0. Column 'p_created_date' was created NOT NULL, but is NULL in the row.
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID 573245097. The text, ntext, or image node at page (1:102), slot 1, text ID 1122172928 is not referenced.
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID 573245097. The text, ntext, or image node at page (1:102), slot 2, text ID 1122238464 is not referenced.
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 3 consistency errors in table 'page_data' (object ID 573245097).
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 3 consistency errors in database 'IMM481_Nidderdale_Visitor'.
[Microsoft][ODBC SQL Server Driver][SQL Server]repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (IMM481_Nidderdale_Visitor ).

    The following errors were found:

[Microsoft][ODBC SQL Server Driver][SQL Server]Row error: Object ID 573245097, index ID 0, page ID (1:170), row ID 0. Column 'p_created_date' was created NOT NULL, but is NULL in the row.
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID 573245097. The text, ntext, or image node at page (1:102), slot 1, text ID 1122172928 is not referenced.
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID 573245097. The text, ntext, or image node at page (1:102), slot 2, text ID 1122238464 is not referenced.
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 3 consistency errors in table 'page_data' (object ID 573245097).
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 3 consistency errors in database 'IMM481_Nidderdale_Visitor'.
[Microsoft][ODBC SQL Server Driver][SQL Server]repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (IMM481_Nidderdale_Visitor ).
    ** Execution Time: 0 hrs, 0 mins, 1 secs **

I'm trying to see the extent of the problem by running:



Begin Tran TranStart
DBCC CheckDB ('IMM481_Nidderdale_Visitor', Repair_Allow_Data_Loss)

However, SQL Server is bitching about the db not being in Single User Mode. I've refreshed the process info to only find the sa using the db.

What have I missed?

Many thanks,

Drew
(You will get sick of this name)

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2005-11-22 : 19:29:31

You need to put the database into single_user mode for repair to run - simply having one user is not enough.

alter database foo set single_user
go

Repair will delete the two unreferenced text nodes. Given that these are the only errors, you're not going to lose any data that any rows still have links to.

Did anything occur to make you run CHECKDB or is it part of regular maintenance?

Thanks

Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)

drewsalem
Constraint Violating Yak Guru

304 Posts

Posted - 2005-11-23 : 05:29:10

Thanks for replying Paul.

I went to a Launch Event yesterday. Ooh, it was so good! And we got SQL Server 2005 Standard Edition freebies! I'm all over the eLearning courses and webcasts now. And free beer!

I'd love to work for MS one day... (sigh)...

quote:
You need to put the database into single_user mode for repair to run - simply having one user is not enough.

Aha! Better wait till tonight then.

quote:
Did anything occur to make you run CHECKDB or is it part of regular maintenance?

Part of new maintenance on databases that were never administered before I came along. This was the first CheckDB it's had.

So I know a dba who has the attitude of "if it's not broke, stay the hell a way from it". As no users have complained of any problems,

1. Would this apply here?

2. How come there are unreferenced text nodes? If a column is set to Not Null, what forces Null values? I performed a query on the reported column searching for Null values, but found none.

Drew

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2005-11-23 : 12:23:27

No, doesn't apply - you need to fix all CHECKDB errors. No telling how long this has been there since its the first CHECKDB its had.

Which version of SQL Server are you using? (incl SP number)

Nothing to do with NULL values - this is a text node that doesn't have any referencing data rows. Simply running repair should clean them up.

What's stopping you applying for a job here?

Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)

drewsalem
Constraint Violating Yak Guru

304 Posts

Posted - 2005-11-23 : 13:42:58

quote:
Which version of SQL Server are you using? (incl SP number)

2000, Service Pack 3.(8.00760)

Is there a reference or book that will tell you exactly what these consistency errors mean, saves me asking in the future. BOL does not seem to drill down this far here.

quote:
What's stopping you applying for a job here?

I need to feel super confident (and so super employable) with what I know before I do that.

Laters,

Drew

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2005-11-23 : 13:58:50

Look on MSDN and in the latest BOL update for SQL Server 2000 - each DBCC error is explained in full. What isn't there is how to interpret them all in combination - that's far from expressible in a coherent manner and is just held in a handful of people's heads. I will be writing a whitepaper that works through a few common scenarios when I can find the time.

Thanks

Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)

drewsalem
Constraint Violating Yak Guru

304 Posts

Posted - 2005-11-23 : 14:40:47

One last question. More of a wondering, really.

I quickly ran the repair_allow_data_loss before I left, (it's 19:30 over here) and as you said, it fixed all the rows.
It identified the offending rows with different IDs. In the instance that real user-data was deleted as a result of this DBCC command, how do I identify what that data was? Is it given after running the command? One would need to tell the business the level of damage occured.

I can't beleive you actually wrote DBCC INDEXDEFRAG, SHOWCONTIG, CHECKDB/repair. You're famous.

Drew

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2005-11-23 : 15:13:02

There's no easy way to tell what's been deleted - for a single error its easy but for multiple errors it gets very complicated and could be a lot of data.

The basic strategy is not to use repair - get a reliable HA/DR strategy that satisfy your business requirements. Repair is a last resort.

Thanks

Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)

drewsalem
Constraint Violating Yak Guru

304 Posts

Posted - 2005-11-24 : 11:37:14

Paul,

Upon running the same check on the remaining databases, the following was the result. Can you please confirm, to the best of your knowledge, the same applies to the remaining databases as before, and that repair_allow_data_loss will delete the unreferenced nodes, and not any user data.

Thanks again.

Drew



Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server 'W23CMS' as 'HBCDOM01\Administrator' (trusted)
Starting maintenance plan 'Non System DB:' on 23/11/2005 17:53:18
[1] Database IMM48_HBC_Forum: Check Data and Index Linkage...

    ** Execution Time: 0 hrs, 0 mins, 1 secs **

[2] Database IMM48_HBC_Live: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 8993: [Microsoft][ODBC SQL Server Driver][SQL Server]Object ID 573245097, forwarding row page (1:170), slot 0 points to page (1:10481), slot 12. Did not encounter forwarded row. Possible allocation error.
[Microsoft][ODBC SQL Server Driver][SQL Server]Row error: Object ID 573245097, index ID 0, page ID (1:10481), row ID 12. Column 'p_created_date' was created NOT NULL, but is NULL in the row.
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID 573245097. The text, ntext, or image node at page (1:102), slot 1, text ID 1122172928 is not referenced.
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID 573245097. The text, ntext, or image node at page (1:102), slot 2, text ID 1122238464 is not referenced.
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 4 consistency errors in table 'page_data' (object ID 573245097).
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 4 consistency errors in database 'IMM48_HBC_Live'.
[Microsoft][ODBC SQL Server Driver][SQL Server]repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (IMM48_HBC_Live ).

    The following errors were found:

[Microsoft][ODBC SQL Server Driver][SQL Server]Object ID 573245097, forwarding row page (1:170), slot 0 points to page (1:10481), slot 12. Did not encounter forwarded row. Possible allocation error.
[Microsoft][ODBC SQL Server Driver][SQL Server]Row error: Object ID 573245097, index ID 0, page ID (1:10481), row ID 12. Column 'p_created_date' was created NOT NULL, but is NULL in the row.
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID 573245097. The text, ntext, or image node at page (1:102), slot 1, text ID 1122172928 is not referenced.
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID 573245097. The text, ntext, or image node at page (1:102), slot 2, text ID 1122238464 is not referenced.
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 4 consistency errors in table 'page_data' (object ID 573245097).
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 4 consistency errors in database 'IMM48_HBC_Live'.
[Microsoft][ODBC SQL Server Driver][SQL Server]repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (IMM48_HBC_Live ).
    ** Execution Time: 0 hrs, 0 mins, 6 secs **

[3] Database IMM481_Nidderdale_AONB: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 8993: [Microsoft][ODBC SQL Server Driver][SQL Server]Object ID 573245097, forwarding row page (1:170), slot 0 points to page (1:2240), slot 1. Did not encounter forwarded row. Possible allocation error.
[Microsoft][ODBC SQL Server Driver][SQL Server]Row error: Object ID 573245097, index ID 0, page ID (1:2240), row ID 1. Column 'p_created_date' was created NOT NULL, but is NULL in the row.
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID 573245097. The text, ntext, or image node at page (1:102), slot 1, text ID 1122172928 is not referenced.
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID 573245097. The text, ntext, or image node at page (1:102), slot 2, text ID 1122238464 is not referenced.
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 4 consistency errors in table 'page_data' (object ID 573245097).
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 4 consistency errors in database 'IMM481_Nidderdale_AONB'.
[Microsoft][ODBC SQL Server Driver][SQL Server]repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (IMM481_Nidderdale_AONB ).

    The following errors were found:

[Microsoft][ODBC SQL Server Driver][SQL Server]Object ID 573245097, forwarding row page (1:170), slot 0 points to page (1:2240), slot 1. Did not encounter forwarded row. Possible allocation error.
[Microsoft][ODBC SQL Server Driver][SQL Server]Row error: Object ID 573245097, index ID 0, page ID (1:2240), row ID 1. Column 'p_created_date' was created NOT NULL, but is NULL in the row.
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID 573245097. The text, ntext, or image node at page (1:102), slot 1, text ID 1122172928 is not referenced.
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID 573245097. The text, ntext, or image node at page (1:102), slot 2, text ID 1122238464 is not referenced.
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 4 consistency errors in table 'page_data' (object ID 573245097).
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 4 consistency errors in database 'IMM481_Nidderdale_AONB'.
[Microsoft][ODBC SQL Server Driver][SQL Server]repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (IMM481_Nidderdale_AONB ).
    ** Execution Time: 0 hrs, 0 mins, 1 secs **

[4] Database IMM481_Nidderdale_Visitor: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 8970: [Microsoft][ODBC SQL Server Driver][SQL Server]Row error: Object ID 573245097, index ID 0, page ID (1:170), row ID 0. Column 'p_created_date' was created NOT NULL, but is NULL in the row.
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID 573245097. The text, ntext, or image node at page (1:102), slot 1, text ID 1122172928 is not referenced.
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID 573245097. The text, ntext, or image node at page (1:102), slot 2, text ID 1122238464 is not referenced.
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 3 consistency errors in table 'page_data' (object ID 573245097).
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 3 consistency errors in database 'IMM481_Nidderdale_Visitor'.
[Microsoft][ODBC SQL Server Driver][SQL Server]repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (IMM481_Nidderdale_Visitor ).

    The following errors were found:

[Microsoft][ODBC SQL Server Driver][SQL Server]Row error: Object ID 573245097, index ID 0, page ID (1:170), row ID 0. Column 'p_created_date' was created NOT NULL, but is NULL in the row.
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID 573245097. The text, ntext, or image node at page (1:102), slot 1, text ID 1122172928 is not referenced.
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID 573245097. The text, ntext, or image node at page (1:102), slot 2, text ID 1122238464 is not referenced.
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 3 consistency errors in table 'page_data' (object ID 573245097).
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 3 consistency errors in database 'IMM481_Nidderdale_Visitor'.
[Microsoft][ODBC SQL Server Driver][SQL Server]repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (IMM481_Nidderdale_Visitor ).
    ** Execution Time: 0 hrs, 0 mins, 1 secs **

[5] Database Northwind: Check Data and Index Linkage...

    ** Execution Time: 0 hrs, 0 mins, 1 secs **

[6] Database pubs: Check Data and Index Linkage...

    ** Execution Time: 0 hrs, 0 mins, 1 secs **

Deleting old text reports...    0 file(s) deleted.

End of maintenance plan 'Non System DB:' on 23/11/2005 17:53:24
SQLMAINT.EXE Process Exit Code: 1 (Failed)

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2005-11-24 : 12:29:01

Scratch everything I've said so far - I missed the fact that you've got another error too (8970 - the NOT NULL one). The repair for this will delete the row (as there's no way for us to know what value to put in for the NOT NULL column) and you'll lose data.

This error is causing the row to be not processed and so the two text nodes are seen as orphaned - all these errors are linked and the root cause is the NULL column.

So, running repair will delete that row. You should be able to get around this by selecting everything from the table and looking for the NULL p_creation_date and then manually changing it. Don't use a 'WHERE p_creation_date IS NULL' clause, as I suspect the optimizer will short-circuit the query because of the declared relational constraint.

Sorry about that.

Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)

Laxminarayana
Starting Member

2 Posts

Posted - 2010-05-16 : 10:02:19

Hi,

After running DBCC checktable with alow data loss, i got below messages.

Repair: Deleted text column, text ID 6434390016, for object ID 117575457 on page (1:14314522), slot 22.
Repair: Deleted text column, text ID 6434652160, for object ID 117575457 on page (1:14314522), slot 26.
Repair: Deleted text column, text ID 6435438592, for object ID 117575457 on page (1:14314522), slot 38.
Repair: Deleted text column, text ID 6436028416, for object ID 117575457 on page (1:14314522), slot 46.
Repair: Deleted text column, text ID 6436093952, for object ID 117575457 on page (1:14314522), slot 47.
Repair: Deleted text column, text ID 6436159488, for object ID 117575457 on page (1:14314522), slot 48.
Repair: Deleted text column, text ID 6436225024, for object ID 117575457 on page (1:14314522), slot 49.
Repair: Deleted text column, text ID 6438977536, for object ID 117575457 on page (1:14314522), slot 91.
Repair: Deleted text column, text ID 6439043072, for object ID 117575457 on page (1:14314522), slot 92.
Repair: Deleted text column, text ID 6439436288, for object ID 117575457 on page (1:14314522), slot 98.
Repair: Deleted text column, text ID 6439698432, for object ID 117575457 on page (1:14314522), slot 102.
Repair: Deleted text column, text ID 6439763968, for object ID 117575457 on page (1:14314522), slot 103.
Repair: Deleted text column, text ID 6439829504, for object ID 117575457 on page (1:14314522), slot 104.
Repair: Deleted text column, text ID 6439895040, for object ID 117575457 on page (1:14314522), slot 105.
Repair: Deleted text column, text ID 6440288256, for object ID 117575457 on page (1:14314522), slot 111.

can any one pls explain in detail?

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-05-16 : 10:19:58

Please don't hijack an old thread. Please don't post questions twice. Asked and answered here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=144677

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources