Keys out of order (DBCC CHECKDB)

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

Keys out of order (DBCC CHECKDB)

Author

Topic

net_chair1
Starting Member

5 Posts

Posted - 2009-04-27 : 12:44:01

Hi there,
I have the table, I could add new records into it and view it. Meantime, when ‘delete’ procedure is triggered I got following error message:
[Microsoft][ODBC SQL Server Driver][SQL Server] Could not find the index entry for RID '6cdbfc076000101e0' in index page (1:627), index ID 3, database 'Reports'.

I run DBCC CHECKDB with the following output:
Table error: Object ID 1977058079, Index ID 3. Keys out of order on page
(1:627), slots 154 and 155.
DBCC results for 'Reports'.
DBCC results for 'sysobjects'.
There are 37 rows in 1 pages for object 'sysobjects'.
DBCC results for 'sysindexes'.
There are 48 rows in 3 pages for object 'sysindexes'.
DBCC results for 'syscolumns'.
There are 300 rows in 5 pages for object 'syscolumns'.
DBCC results for 'systypes'.
There are 26 rows in 1 pages for object 'systypes'.
DBCC results for 'syscomments'.
There are 113 rows in 17 pages for object 'syscomments'.
DBCC results for 'sysfiles1'.
There are 2 rows in 1 pages for object 'sysfiles1'.
DBCC results for 'syspermissions'.
There are 18 rows in 1 pages for object 'syspermissions'.
DBCC results for 'sysusers'.
There are 12 rows in 1 pages for object 'sysusers'.
DBCC results for 'sysproperties'.
There are 0 rows in 0 pages for object 'sysproperties'.
DBCC results for 'sysdepends'.
There are 305 rows in 1 pages for object 'sysdepends'.
DBCC results for 'sysreferences'.
There are 0 rows in 0 pages for object 'sysreferences'.
DBCC results for 'sysfulltextcatalogs'.
There are 0 rows in 0 pages for object 'sysfulltextcatalogs'.
DBCC results for 'sysfulltextnotify'.
There are 0 rows in 0 pages for object 'sysfulltextnotify'.
DBCC results for 'sysfilegroups'.
There are 1 rows in 1 pages for object 'sysfilegroups'.
DBCC results for 'Entry'.
There are 20846 rows in 121 pages for object 'Entry'.
CHECKDB found 0 allocation errors and 1 consistency errors in table 'Entry'
(object ID 1977058079).
DBCC results for 'Details'.
There are 40845 rows in 201 pages for object 'Details'.
DBCC results for 'Information'.
There are 1 rows in 1 pages for object 'Information'.
DBCC results for 'GlobalIdent'.
There are 2 rows in 1 pages for object 'GlobalIdent'.
CHECKDB found 0 allocation errors and 1 consistency errors in database 'Reports'.
repair_rebuild is the minimum repair level for the errors found by DBCC CHECKDB
(Reports ).
DBCC execution completed. If DBCC printed error messages, contact your system
administrator.

I was reading some other posts, which have similar types of error. SP4 should correct some of the existed error (2 out of 3 if I’m not mistaken). Also, by running 'repair_rebuild' command, DB should be corrected.
Is that simply HW problem or something else? Do I have to for some other possible problem?

Regards,

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-04-27 : 14:06:38

Repair_rebuild should fix it, though you have to take the DB into single user mode to do so. Try dropping the index in question (index with ID 3 on table Entry) and then recreating it. That should fix the errors.

What version of SQL 2000 are you on? (SELECT @@version)

--
Gail Shaw
SQL Server MVP

net_chair1
Starting Member

5 Posts

Posted - 2009-04-27 : 14:12:03

Thanks Gila,
SQL Server 2000 - 8.00.760. I know it is old, but it is what customer have, so we have to stick with it. Also they have only SP3.

Thanks

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-04-27 : 15:49:09

Have a look at these kb articles, see if any could apply
http://support.microsoft.com/kb/822747
http://support.microsoft.com/kb/929440

One is fixed in SP4, the other is fixed in a post-SP4 hotfix.
Will the customer be open to patching this system?

--
Gail Shaw
SQL Server MVP

net_chair1
Starting Member

5 Posts

Posted - 2009-04-27 : 15:54:32

I think so, this is something needs to be done. They are reasonable :)
If it prevents from losing data I don't see why they'll object.

Thanks,

net_chair1
Starting Member

5 Posts

Posted - 2009-04-29 : 18:00:27

seems i have another issue now.
bellow the code i use:
...
m_pConn->Open (bstrConnectionString,_bstr_t ( "" ),_bstr_t ( "" ),adConnectUnspecified );
pCommand.CreateInstance (__uuidof (Command));
pCommand->ActiveConnection = m_pConn;
try
{
// switch to single mode, force it
pCommand->CommandText = "ALTER DATABASE Reports SET SINGLE_USER WITH ROLLBACK IMMEDIATE";
pRecordSet = pCommand->Execute(NULL, NULL, adCmdText);
}
catch(_com_error &e)
{}
// change command to validate integrity
pCommand->CommandText ="dbcc checkdb ('Customers', repair_rebuild)";

// triger correction command
try
{
pRecordSet = pCommand->Execute(NULL, NULL, adCmdText);
}
catch(_com_error &e)
{
cout<<"Failed to execute Single_User Command " << endl;
}

// now put back multi_user access
pCommand->CommandText ="alter database Customers set multi_user";
try
{
pCommand->Execute(NULL, NULL, adCmdText);
}
...

Looks ok, but i'm keep getting exceptions: it is either 'DB_E_ERRORSINCOMMAND' or IDispatch error #3092 ([Microsoft][ODBC SQL Server Driver][SQL Server] DBCC results for 'Customers'). What is more interesting, consistency error(s) are corrected

Any ideas?

Regards

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-04-30 : 04:32:11

Um, where's that code from and why are you automating a repair? Repairs (even repair rebuild) shouldn't just be done without investigation first.

--
Gail Shaw
SQL Server MVP

net_chair1
Starting Member

5 Posts

Posted - 2009-04-30 : 10:04:48

Thanks Gila,
Code from the utility application we have (C++). I run the same command from cmd prompt - to test it.
About DBCC - not my 'forte'. You suggesting that I must run diagnostic first and then try to repair if necessary.(?)

Regards,

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-04-30 : 12:56:29

Yes. It's a very, very, very bad idea to trigger automatic repairs. Run the check regularly (SQL job, maintenance plan) and check the output. If there are errors then evaluate what need to be done and when.

Repair is often the last resort for fixing corruptions, not something that should be automatically kicked off without human intervention.

This article may be of interest - [url]http://www.sqlservercentral.com/articles/65804/[/url]

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources