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

Author

Topic

Alexey
Starting Member

8 Posts

Posted - 2006-07-18 : 08:15:06

OS & SQL Version:
SELECT @@VERSION
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

It's a working database. It's set up to make a backup every night at 4:15 AM when the load is minimal. When making a backup it should run DBCC CHECKDB without any repair option on. One day i got a notification that backup didn't completed. I checked the errorlogs for the day between last successful backup and the next one failed. Logs are clean.

Here is the result of "DBCC CHECKDB WITH NO_INFOMSGS, ALL_ERRORMSGS":
Server: Msg 8951, Level 16, State 1, Line 1
Table error: Table 'Sessions' (ID 398624463). Missing or invalid key in index 'PK_Sessions' (ID 2) for the row:
Server: Msg 8955, Level 16, State 1, Line 1
Data row (1:7420:37) identified by (RID = (1:7420:37) ) has index values (SessionId = 3539435).
CHECKDB found 0 allocation errors and 1 consistency errors in table 'Sessions' (object ID 398624463).
CHECKDB found 0 allocation errors and 1 consistency errors in database 'Accounting'.
repair_fast is the minimum repair level for the errors found by DBCC CHECKDB (Accounting ).

Not sure if sp_helpindex results are necessary, but may be it will help:
IX_Sessions_LoginTime nonclustered located on PRIMARY LoginTime
IX_Sessions_NasIp nonclustered located on PRIMARY NasIp
IX_Sessions_NasSessionId nonclustered located on PRIMARY NasSessionId
IX_Sessions_User nonclustered located on PRIMARY UserId
PK_Sessions nonclustered, unique, primary key located on PRIMARY SessionId
Sessions0 nonclustered located on PRIMARY LoginTime, BytesIn

REPAIR_FAST does not help. The error isn't cleared.
I have two questions about that issue.
1) How i can dig into that problem? For example i would like to simply look at the row causing problems, but i just don't know how to select rows referencing them by RID. Is it piossible? Actually i don't quite understand what DBCC output means. :(
2) Second question is of course how to correct that problem. :)

Of course i can restore from backup, but i'd like to know if there are other ways? Maybe without loosing all the information which was entered during the day? I can afford loosing several rows from that table, but loosing all info entered is not very good solution. No, i don't need backup strategy involving several DB backup during the day. :) Just don't want to reenter all the info, but it can be done if there are no other ways. :)

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-18 : 08:41:48

You could try rebuilding the index PK_Sessions.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Alexey
Starting Member

8 Posts

Posted - 2006-07-18 : 11:16:53

Actually, REPAIR_REBUILD doesn't help either.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-18 : 11:53:16

Not in checkdb - recreate the index on the table.
I would have thought checkdb would do it if this works but it often does if it's just a problem with an index.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Alexey
Starting Member

8 Posts

Posted - 2006-07-18 : 12:43:50

Well, actually this can be tricky. As you can see it's a "Primary key" index, so it can't be just dropped.
DROP INDEX Sessions.PK_Sessions
resulted in:
Server: Msg 3723, Level 16, State 4, Line 1
An explicit DROP INDEX is not allowed on index 'Sessions.PK_Sessions'. It is being used for PRIMARY KEY constraint enforcement.

And CREATE INDEX WITH DROP_EXISTING should not work either, bekause when using DROP_EXISTING the index should be consistent and in mine case it's not. Anyway,
CREATE UNIQUE INDEX [PK_Sessions] ON [dbo].[Sessions] ([SessionId])
WITH FILLFACTOR = 90, DROP_EXISTING
ON [PRIMARY]
resulted in:
Server: Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 2. Most significant primary key is '3539435'.
The statement has been terminated.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-18 : 14:56:48

Actually I was thinking of dbcc dbreindex but if you have duplicate entries that won't work either.

Try copying the data into another table - if that works then replace the old table with the new.
If it doesn't because of the duplicate rows then delete whatever is incorrect
If you can't do that then you have a choice - go back to the last backup or recover as much data as you can using indexes to access it.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Alexey
Starting Member

8 Posts

Posted - 2006-07-19 : 04:46:43

Big thanks Nigel. My second question is answered. By deleteing respective row i solved the problem.
But my first question in the first post is not answered so far. :)
I had to delete the older of the two conflicting rows. It was 4 months old and it was a correct row as i guess. I was not able to lockup the conflicting row which appeared 2 days ago and caused indexing errors. So maybe someone still can explain how i could have locked up the conflicting error and delete it by using DBCC CHECKDB output?

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-19 : 05:48:38

Well
Data row (1:7420:37) identified by (RID = (1:7420:37) ) has index values (SessionId = 3539435).
probably identifies the data in error but it's not that easy to find it
Look at
http://www.nigelrivett.net/SQLAdmin/PageStructure.html
The method for finding the page is probably similar.
If you want to do this get a copy of Inside SQL Server which explains the structures in more depth.

Must admit I've never seen a duplicate entry in a table that violates a unique index.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2006-07-19 : 12:32:30

The problem is that there are two rows in the table with SessionId = 3539435. This smells like a bug to me - somehow the second row was allowed to be inserted or it resulted from a failed rollback of an update to the first row - something like that. I don't see anything in our bug database.

Were there any problems with the database between the previous backup and the one that failed?

Side question: why no clustered index?

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

Alexey
Starting Member

8 Posts

Posted - 2006-07-21 : 04:53:03

The situation gets to the point where i completely lost the understanding of what's happening. As i did said at Jul 19, 2006 11:40 AM i deleted one of the conflicting rows, rebuilt the index and subsequent DBCC CHECKDB indicated no errors.
Maintenance plan intended to check the integrity and backup the database at Jul 20, 2006 04:25 AM worked fine without any errors.
The same maintenance plan (it's scheduled to run every night) at Jul 21, 2006 04:25 AM indicated that there was once again some errors with integrity and the plan failed to finish.
I run DBCC CHECKDB and to my surprise found out that i got the same error i had 2 days ago!!!
The database WAS NOT ROLLED BACK !!! I mean i DID NOT used a backup to restore the DB. Here are the SQL Server logs and result of DBCC.

--this is i run a dbcc after deleted the row--
2006-07-19 11:12:43.05 spid56 DBCC CHECKDB (Accounting) executed by MEGANET\Alexey found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 6 seconds.
--this is i manually run a backup procedure--
2006-07-19 11:38:44.63 spid64 DBCC CHECKDB (Accounting) executed by MEGANET\GOR$ found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 6 seconds.
2006-07-19 11:40:13.81 spid64 DBCC CHECKDB (Accounting) executed by MEGANET\GOR$ found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 6 seconds.
2006-07-19 11:40:52.77 backup Database backed up: Database: Accounting, creation date(time): 2006/02/02(11:33:36), pages dumped: 43922, first LSN: 36721:6486:1, last LSN: 36721:6532:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'C:\BackUp\Accounting_db_200607191140.BAK'}).
--this maintenance was run by schedule - status ok--
2006-07-20 04:25:12.67 spid56 DBCC CHECKDB (Accounting) executed by MEGANET\GOR$ found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 11 seconds.
2006-07-20 04:55:09.09 spid56 DBCC CHECKDB (Accounting) executed by MEGANET\GOR$ found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 7 seconds.
2006-07-20 04:55:41.15 backup Database backed up: Database: Accounting, creation date(time): 2006/02/02(11:33:36), pages dumped: 43850, first LSN: 36760:3288:1, last LSN: 36760:3293:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'C:\BackUp\Accounting_db_200607200455.BAK'}).
2006-07-20 05:05:06.97 spid54 DBCC CHECKDB (Accounting) executed by MEGANET\GOR$ found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 6 seconds.
2006-07-20 05:05:36.25 backup Log backed up: Database: Accounting, creation date(time): 2006/02/02(11:33:36), first LSN: 36671:11999:1, last LSN: 36760:3333:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'C:\Accounting_tlog_200607200505.TRN'}).
--this maintenance was run by schedule - status failed--
2006-07-21 04:25:16.67 spid56 DBCC CHECKDB (Accounting) executed by MEGANET\GOR$ found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 15 seconds.
2006-07-21 04:55:09.06 spid54 DBCC CHECKDB (Accounting) executed by MEGANET\GOR$ found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 7 seconds.
2006-07-21 04:55:48.29 backup Database backed up: Database: Accounting, creation date(time): 2006/02/02(11:33:36), pages dumped: 43826, first LSN: 36883:12829:1, last LSN: 36883:12836:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'C:\BackUp\Accounting_db_200607210455.BAK'}).
2006-07-21 05:05:08.59 spid56 DBCC CHECKDB (Accounting) executed by MEGANET\GOR$ found 2 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 7 seconds.
--this is dbcc i run to figure what's wrong--
2006-07-21 10:48:06.50 spid54 DBCC CHECKDB (Accounting) executed by MEGANET\Alexey found 4 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 8 seconds.

The maintenance plan consist of following procedures:
1) Integrity check, no repair options set
2) Optimization job, reorganize data and index pages, change free space per page to 10% (maybe this step caused problems?)
3) Complete backup with verify
4) Transaction log backup with verify

As you can see no any errors were logged in between successful backup (Jul 20) and failed one (Jul 21).

Here is DBCC CHECKDB output:
Server: Msg 8952, Level 16, State 1, Line 1
Table error: Database 'Accounting', index 'Sessions.PK_Sessions' (ID 398624463) (index ID 2). Extra or invalid key for the keys:
Server: Msg 8956, Level 16, State 1, Line 1
Index row (1:22547:278) with values (SessionId = 3539435) points to the data row identified by (RID = (1:7420:37)).
Server: Msg 8952, Level 16, State 1, Line 1
Table error: Database 'Accounting', index 'Sessions.IX_Sessions_NasIp' (ID 398624463) (index ID 5). Extra or invalid key for the keys:
Server: Msg 8956, Level 16, State 1, Line 1
Index row (1:29387:177) with values (NasIp = '297.26.162.29') points to the data row identified by (RID = (1:7420:37)).
Server: Msg 8951, Level 16, State 1, Line 1
Table error: Table 'Sessions' (ID 398624463). Missing or invalid key in index 'Sessions0' (ID 20) for the row:
Server: Msg 8955, Level 16, State 1, Line 1
Data row (1:193:24) identified by (RID = (1:193:24) ) has index values (LoginTime = Oct 10 2005 2:22PM and BytesIn = 240047).
Server: Msg 8952, Level 16, State 1, Line 1
Table error: Database 'Accounting', index 'Sessions.Sessions0' (ID 398624463) (index ID 20). Extra or invalid key for the keys:
Server: Msg 8956, Level 16, State 1, Line 1
Index row (1:32319:223) with values (LoginTime = Oct 10 2005 2:22PM and BytesIn = 239872) points to the data row identified by (RID = (1:193:24)).
CHECKDB found 0 allocation errors and 4 consistency errors in table 'Sessions' (object ID 398624463).
CHECKDB found 0 allocation errors and 4 consistency errors in database 'Accounting'.
repair_fast is the minimum repair level for the errors found by DBCC CHECKDB (Accounting ).

It looks simmilar to what i got at 18 Jul. The one at 18 Jul had same 4 errors and after REPAIR_FAST only one error remained. The one with primary key index error.

Now i would like to know what's happeneing? I'm absolutely sure database WAS NOT rolled back! Can please somebody help?

P.S. Paul, it was not me who designed the DB struture. I'm just maintaining it. So i'm not sure why all indexes was made no clustered. Do you think clustered index would be better? How it will influence on DB performance?

Alexey
Starting Member

8 Posts

Posted - 2006-07-21 : 05:13:58

Well, i executed repair_fast TWO times and it cleared the errors. After first run it told me it fixed 4 errors. DBCC CHECKDB still reported 2 errors in the table. Second repair_fast reported fixing 2 errors. Subsequent DBCC CHECKDB indicated no errors.

I'm completely lost, what was that? :)

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-21 : 05:38:25

I would consider crating a new database and copying the data into it and scrapping this one.
Do you have problems with anything else on the same hardware? Try checking the disks.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Alexey
Starting Member

8 Posts

Posted - 2006-07-21 : 06:28:41

No, there are no any other errors with other software on the server. Event Logs are clean. Hardware tests indicated no errors...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources