Checkdb 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

Checkdb Error

Author

Topic

advsteve
Starting Member

8 Posts

Posted - 2007-05-07 : 18:41:33

Hi, I'm hoping somebody can help with an error received when actions are performed on any record. The error is received in a front end program that we cannot get support for. I'm not too familiar with SQL Server but can get by, does anybody have suggestions? Thanks

S1000:[Microsoft]pODBC SQL Server DRiver][SQL Server]Could not find the index entry for RID '16565ba0100' in index page [1:66796], index ID 0, database 'db1'.

The results found from checkdb are:

DBCC results for 'DB1'.
DBCC results for 'sysobjects'.
There are 155 rows in 3 pages for object 'sysobjects'.
DBCC results for 'sysindexes'.
There are 300 rows in 14 pages for object 'sysindexes'.
DBCC results for 'syscolumns'.
There are 1327 rows in 24 pages for object 'syscolumns'.
DBCC results for 'systypes'.
There are 26 rows in 1 pages for object 'systypes'.
DBCC results for 'syscomments'.
There are 127 rows in 15 pages for object 'syscomments'.
DBCC results for 'sysfiles1'.
There are 2 rows in 1 pages for object 'sysfiles1'.
DBCC results for 'syspermissions'.
There are 49 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 309 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 'F_Company'.
There are 0 rows in 0 pages for object 'F_Company'.
DBCC results for 'F_Cr_Detail'.
There are 222 rows in 21 pages for object 'F_Cr_Detail'.
DBCC results for 'F_Department'.
There are 6 rows in 1 pages for object 'F_Department'.
DBCC results for 'F_Document'.
There are 0 rows in 0 pages for object 'F_Document'.
DBCC results for 'F_Entry'.
There are 231241 rows in 2387 pages for object 'F_Entry'.
DBCC results for 'F_GL_Asset'.
There are 0 rows in 0 pages for object 'F_GL_Asset'.
DBCC results for 'F_GL_Body'.
There are 2 rows in 1 pages for object 'F_GL_Body'.
DBCC results for 'F_Letter'.
There are 100 rows in 2 pages for object 'F_Letter'.
DBCC results for 'F_Pm_Allocate'.
There are 0 rows in 0 pages for object 'F_Pm_Allocate'.
DBCC results for 'F_Pm_Auto_Entry'.
There are 3 rows in 1 pages for object 'F_Pm_Auto_Entry'.
DBCC results for 'F_Pm_Expense'.
There are 13 rows in 1 pages for object 'F_Pm_Expense'.
DBCC results for 'F_Pm_Job_Type'.
There are 0 rows in 0 pages for object 'F_Pm_Job_Type'.
DBCC results for 'F_Pm_Key'.
There are 2408 rows in 15 pages for object 'F_Pm_Key'.
DBCC results for 'F_Pm_Landlord'.
There are 1560 rows in 84 pages for object 'F_Pm_Landlord'.
DBCC results for 'F_Preferences'.
There are 0 rows in 0 pages for object 'F_Preferences'.
DBCC results for 'F_Printer'.
There are 0 rows in 0 pages for object 'F_Printer'.
DBCC results for 'F_Reports'.
There are 1 rows in 1 pages for object 'F_Reports'.
DBCC results for 'F_Suburb'.
There are 18 rows in 1 pages for object 'F_Suburb'.
DBCC results for 'F_System'.
There are 1 rows in 1 pages for object 'F_System'.
DBCC results for 'F_TaskType'.
There are 2 rows in 1 pages for object 'F_TaskType'.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1205579333, index ID 0: Page (1:66796) could not be processed. See other errors for details.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 1205579333, index ID 0, page (1:66796). Test (m_freeCnt == freeCnt) failed. Values are 8 and 251.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 1205579333, index ID 0, page (1:66796). Test (emptySlotCnt == 0) failed. Values are 1 and 0.
DBCC results for 'F_Terminal'.
There are 8 rows in 1 pages for object 'F_Terminal'.
DBCC results for 'F_Type'.
There are 7 rows in 1 pages for object 'F_Type'.
DBCC results for 'F_User'.
There are 1 rows in 1 pages for object 'F_User'.
DBCC results for 'F_GL_Reconcile'.
There are 2908 rows in 12 pages for object 'F_GL_Reconcile'.
DBCC results for 'F_Pm_La_Pay'.
There are 0 rows in 0 pages for object 'F_Pm_La_Pay'.
DBCC results for 'F_Pm_Property'.
There are 2293 rows in 121 pages for object 'F_Pm_Property'.
DBCC results for 'F_Cr_Entry'.
There are 3 rows in 1 pages for object 'F_Cr_Entry'.
DBCC results for 'F_Cr_Type'.
There are 0 rows in 0 pages for object 'F_Cr_Type'.
DBCC results for 'F_GL_Entry'.
There are 1190 rows in 11 pages for object 'F_GL_Entry'.
DBCC results for 'F_Pm_P_Analysis'.
There are 0 rows in 0 pages for object 'F_Pm_P_Analysis'.
DBCC results for 'F_Pm_Tenant'.
There are 4927 rows in 354 pages for object 'F_Pm_Tenant'.
DBCC results for 'F_Task'.
There are 847 rows in 17 pages for object 'F_Task'.
DBCC results for 'F_Item'.
There are 439788 rows in 5531 pages for object 'F_Item'.
DBCC results for 'F_Pm_Auto_Item'.
There are 2 rows in 1 pages for object 'F_Pm_Auto_Item'.
DBCC results for 'F_Pm_Diary'.
There are 5732 rows in 78 pages for object 'F_Pm_Diary'.
DBCC results for 'F_Pm_Entry'.
There are 671566 rows in 22181 pages for object 'F_Pm_Entry'.
CHECKDB found 0 allocation errors and 3 consistency errors in table 'F_Pm_Entry' (object ID 1205579333).
DBCC results for 'F_Pm_Job'.
There are 4169 rows in 128 pages for object 'F_Pm_Job'.
DBCC results for 'F_Pm_Job_Entry'.
There are 0 rows in 0 pages for object 'F_Pm_Job_Entry'.
DBCC results for 'F_Ftp_Listings'.
There are 0 rows in 0 pages for object 'F_Ftp_Listings'.
DBCC results for 'F_Ftp_System'.
There are 0 rows in 0 pages for object 'F_Ftp_System'.
DBCC results for 'F_RealEnz2'.
There are 0 rows in 0 pages for object 'F_RealEnz2'.
DBCC results for 'F_Security'.
There are 4 rows in 1 pages for object 'F_Security'.
DBCC results for 'F_Auto_Entry'.
There are 0 rows in 0 pages for object 'F_Auto_Entry'.
DBCC results for 'F_Au_Item'.
There are 0 rows in 0 pages for object 'F_Au_Item'.
DBCC results for 'F_Log'.
There are 0 rows in 0 pages for object 'F_Log'.
DBCC results for 'dtproperties'.
There are 0 rows in 0 pages for object 'dtproperties'.
DBCC results for 'F_Account'.
There are 229 rows in 3 pages for object 'F_Account'.
DBCC results for 'F_Analysis'.
There are 0 rows in 0 pages for object 'F_Analysis'.
DBCC results for 'F_Auto_Banking'.
There are 0 rows in 1 pages for object 'F_Auto_Banking'.
DBCC results for 'F_Branch'.
There are 3 rows in 1 pages for object 'F_Branch'.
DBCC results for 'F_Change'.
There are 5 rows in 1 pages for object 'F_Change'.
DBCC results for 'F_Clauses'.
There are 91 rows in 1 pages for object 'F_Clauses'.
CHECKDB found 0 allocation errors and 3 consistency errors in database 'rpm'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (db1 ).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2007-05-07 : 22:27:50

Somehow that page (1:66796) has become corrupt -looks like a record has been corrupted. Did anything happen to the database or server? Any errors in the SQL error log or Windows event logs?

Do you have a backup you can restore from?

Paul Randal
Principal Lead Program Manager, Microsoft SQL Server Core Storage Engine
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)
http://blogs.msdn.com/sqlserverstorageengine/default.aspx

advsteve
Starting Member

8 Posts

Posted - 2007-05-07 : 22:54:29

Thanks Paul..
We have tape backups to go back to, but unfortunately if I go back to backup 3 days of work is lost.

Event logs are clean. Is there a command I can run to fix up the corrupt record?

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2007-05-07 : 23:16:34

Yes - you can run repair but you'll lose a whole page's worth of data which may make your data logically inconsistent if you have constraints or inherent business logic in the database.

Do the following to see what data you'll lose:

dbcc traceon (3604)
go
dbcc page (rpm, 1, 66796, 3)
go

And if the data isn't confidential, post it (or email to prandal@microsoft.com)

Thanks

Paul Randal
Principal Lead Program Manager, Microsoft SQL Server Core Storage Engine
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)
http://blogs.msdn.com/sqlserverstorageengine/default.aspx

advsteve
Starting Member

8 Posts

Posted - 2007-05-07 : 23:38:50

Thanks again for your response, the following is what was generated:

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

PAGE: (1:66796)
---------------

BUFFER:
-------

BUF @0x01490C80
---------------
bpage = 0x517F4000 bhash = 0x00000000 bpageno = (1:66796)
bdbid = 7 breferences = 9 bstat = 0x9
bspin = 0 bnext = 0x00000000

PAGE HEADER:
------------

Page @0x517F4000
----------------
m_pageId = (1:66796) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x0
m_objId = 1205579333 m_indexId = 0 m_prevPage = (1:66795)
m_nextPage = (1:66797) pminlen = 204 m_slotCnt = 31
m_freeCnt = 8 m_freeData = 8122 m_reservedCnt = 0
m_lsn = (3468:7524:3) m_xactReserved = 0 m_xdesId = (0:0)
m_ghostRecCnt = 0 m_tornBits = 0

Allocation Status
-----------------
GAM (1:2) = ALLOCATED SGAM (1:3) = NOT ALLOCATED
PFS (1:64704) = 0x40 ALLOCATED 0_PCT_FULL DIFF (1:6) = NOT CHANGED
ML (1:7) = NOT MIN_LOGGED

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2007-05-08 : 00:02:33

Well the page thinks its empty AND has 31 slots - clearly corrupt. It's not possible to tell how it got corrupt though. I'd go ahead and run repair to fix this.

Paul Randal
Principal Lead Program Manager, Microsoft SQL Server Core Storage Engine
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)
http://blogs.msdn.com/sqlserverstorageengine/default.aspx

advsteve
Starting Member

8 Posts

Posted - 2007-05-08 : 01:32:43

Thanks kindly for the help Paul. Seems to have done the trick (users no longer report the error). At the end it says "CHECKDB fixed 0 allocation errors and 5 consistency errors in database" but I'm guessing it's still OK?

DBCC results for 'F_Pm_Entry'.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1205579333, index ID 0: Page (1:66796) could not be processed. See other errors for details.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 1205579333, index ID 0, page (1:66796). Test (m_freeCnt == freeCnt) failed. Values are 8 and 251.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 1205579333, index ID 0, page (1:66796). Test (emptySlotCnt == 0) failed. Values are 1 and 0.
Server: Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 1205579333, index ID 1. Page (1:66796) was not seen in the scan although its parent (1:66427) and previous (1:66795) refer to it. Check any previous errors.
Server: Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 1205579333, index ID 1. Page (1:66797) is missing a reference from previous page (1:66796). Possible chain linkage problem.
The error has been repaired.
The error has been repaired.
The error has been repaired.
The error has been repaired.
The error has been repaired.
Repair: Page (1:66796) has been deallocated from object ID 1205579333, index ID 0.

Kristen
Test

22859 Posts

Posted - 2007-05-08 : 01:47:15

"I'm guessing it's still OK?"

Perhaps run DBCC CHECKDB WITH NO_INFOMSG one more time and if there are no further errors the DB is repaired.

Kristen

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2007-05-08 : 01:52:30

Looks like you're sorted.

Paul Randal
Principal Lead Program Manager, Microsoft SQL Server Core Storage Engine
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)
http://blogs.msdn.com/sqlserverstorageengine/default.aspx

mcrowley
Aged Yak Warrior

771 Posts

Posted - 2007-05-08 : 11:13:16

Out of curiosity, how is it that the repair output is referencing Object ID 1205579333, index ID 0 and Object ID 1205579333, index ID 1? Are the leaf pages of a clustered index considered index 0 pages be DBCC CHECKDB?

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2007-05-08 : 17:34:30

The messages come from different parts of CHECKDB. The ones referencing index ID 0 are from just looking at the page contents, but the ones referencing index ID 1 are from code that's aggregating b-tree linkage facts in a clustered index. The page is part of the leaf-level of a clustered index. There are a variety of combinations of operations involving creating & dropping indexes that can lead to the index ID on data pages being 1 or 0 and seemingly in disagreement with the existence or not of a clustered index.

Thanks

Paul Randal
Principal Lead Program Manager, Microsoft SQL Server Core Storage Engine
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)
http://blogs.msdn.com/sqlserverstorageengine/default.aspx

advsteve
Starting Member

8 Posts

Posted - 2007-05-08 : 17:49:46

I think I have an even bigger problem now. Users are reporting the same errors again and this is what checkdb outputs (this is only a small amount of it too):

Index row (1:28244:277) with values (PR_SEQ = 1409 and PME_SEQ = 678746) points to the data row identified by ().
Server: Msg 8952, Level 16, State 1, Line 1
Table error: Database 'db1', index 'F_Pm_Entry.PR_SEQ' (ID 1205579333) (index ID 2). Extra or invalid key for the keys:
Server: Msg 8956, Level 16, State 1, Line 1
Index row (1:34144:455) with values (PR_SEQ = 688 and PME_SEQ = 678732) points to the data row identified by ().
Server: Msg 8952, Level 16, State 1, Line 1
Table error: Database 'db1', index 'F_Pm_Entry.PR_SEQ' (ID 1205579333) (index ID 2). Extra or invalid key for the keys:
Server: Msg 8956, Level 16, State 1, Line 1
Index row (1:42384:191) with values (PR_SEQ = 1586 and PME_SEQ = 678739) points to the data row identified by ().
Server: Msg 8952, Level 16, State 1, Line 1
Table error: Database 'db1', index 'F_Pm_Entry.PR_SEQ' (ID 1205579333) (index ID 2). Extra or invalid key for the keys:
Server: Msg 8956, Level 16, State 1, Line 1
Index row (1:45691:330) with values (PR_SEQ = 1896 and PME_SEQ = 678755) points to the data row identified by ().
Server: Msg 8952, Level 16, State 1, Line 1
Table error: Database 'db1', index 'F_Pm_Entry.PR_SEQ' (ID 1205579333) (index ID 2). Extra or invalid key for the keys:
Server: Msg 8956, Level 16, State 1, Line 1
Index row (1:48562:416) with values (PR_SEQ = 1962 and PME_SEQ = 678743) points to the data row identified by ().
Server: Msg 8952, Level 16, State 1, Line 1
Table error: Database 'db1', index 'F_Pm_Entry.PR_SEQ' (ID 1205579333) (index ID 2). Extra or invalid key for the keys:
Server: Msg 8956, Level 16, State 1, Line 1
Index row (1:59929:48) with values (PR_SEQ = 2199 and PME_SEQ = 678742) points to the data row identified by ().
Server: Msg 8952, Level 16, State 1, Line 1
Table error: Database 'db1', index 'F_Pm_Entry.PR_SEQ' (ID 1205579333) (index ID 2). Extra or invalid key for the keys:
Server: Msg 8956, Level 16, State 1, Line 1
Index row (1:66075:402) with values (PR_SEQ = 2350 and PME_SEQ = 678749) points to the data row identified by ().
Server: Msg 8952, Level 16, State 1, Line 1
Table error: Database 'db1', index 'F_Pm_Entry.PR_SEQ' (ID 1205579333) (index ID 2). Extra or invalid key for the keys:
Server: Msg 8956, Level 16, State 1, Line 1
Index row (1:66075:443) with values (PR_SEQ = 2362 and PME_SEQ = 678738) points to the data row identified by ().
Server: Msg 8952, Level 16, State 1, Line 1
Table error: Database 'db1', index 'F_Pm_Entry.PR_SEQ' (ID 1205579333) (index ID 2). Extra or invalid key for the keys:
Server: Msg 8956, Level 16, State 1, Line 1
Index row (1:66075:444) with values (PR_SEQ = 2363 and PME_SEQ = 678735) points to the data row identified by ().
Server: Msg 8952, Level 16, State 1, Line 1
Table error: Database 'db1', index 'F_Pm_Entry.PR_SEQ' (ID 1205579333) (index ID 2). Extra or invalid key for the keys:
Server: Msg 8956, Level 16, State 1, Line 1
Index row (1:66075:446) with values (PR_SEQ = 2364 and PME_SEQ = 678736) points to the data row identified by ().
Server: Msg 8952, Level 16, State 1, Line 1
Table error: Database 'db1', index 'F_Pm_Entry.PR_SEQ' (ID 1205579333) (index ID 2). Extra or invalid key for the keys:
Server: Msg 8956, Level 16, State 1, Line 1
Index row (1:66365:243) with values (PR_SEQ = 0 and PME_SEQ = 678727) points to the data row identified by ().
Server: Msg 8952, Level 16, State 1, Line 1
Table error: Database 'db1', index 'F_Pm_Entry.PR_SEQ' (ID 1205579333) (index ID 2). Extra or invalid key for the keys:
Server: Msg 8956, Level 16, State 1, Line 1
Index row (1:66365:244) with values (PR_SEQ = 0 and PME_SEQ = 678728) points to the data row identified by ().
Server: Msg 8952, Level 16, State 1, Line 1
Table error: Database 'db1', index 'F_Pm_Entry.PR_SEQ' (ID 1205579333) (index ID 2). Extra or invalid key for the keys:
Server: Msg 8956, Level 16, State 1, Line 1
Index row (1:66365:245) with values (PR_SEQ = 0 and PME_SEQ = 678730) points to the data row identified by ().
Server: Msg 8952, Level 16, State 1, Line 1
Table error: Database 'db1', index 'F_Pm_Entry.PR_SEQ' (ID 1205579333) (index ID 2). Extra or invalid key for the keys:
Server: Msg 8956, Level 16, State 1, Line 1
Index row (1:66365:246) with values (PR_SEQ = 0 and PME_SEQ = 678731) points to the data row identified by ().
Server: Msg 8952, Level 16, State 1, Line 1
Table error: Database 'db1', index 'F_Pm_Entry.PR_SEQ' (ID 1205579333) (index ID 2). Extra or invalid key for the keys:
Server: Msg 8956, Level 16, State 1, Line 1
Index row (1:66365:247) with values (PR_SEQ = 0 and PME_SEQ = 678733) points to the data row identified by ().
Server: Msg 8952, Level 16, State 1, Line 1
Table error: Database 'db1', index 'F_Pm_Entry.PR_SEQ' (ID 1205579333) (index ID 2). Extra or invalid key for the keys:
Server: Msg 8956, Level 16, State 1, Line 1
Index row (1:66365:248) with values (PR_SEQ = 0 and PME_SEQ = 678734) points to the data row identified by ().
Server: Msg 8952, Level 16, State 1, Line 1
Table error: Database 'db1', index 'F_Pm_Entry.PR_SEQ' (ID 1205579333) (index ID 2). Extra or invalid key for the keys:
Server: Msg 8956, Level 16, State 1, Line 1
Index row (1:66365:249) with values (PR_SEQ = 0 and PME_SEQ = 678737) points to the data row identified by ().
Server: Msg 8952, Level 16, State 1, Line 1
Table error: Database 'db1', index 'F_Pm_Entry.PR_SEQ' (ID 1205579333) (index ID 2). Extra or invalid key for the keys:
Server: Msg 8956, Level 16, State 1, Line 1
Index row (1:66365:250) with values (PR_SEQ = 0 and PME_SEQ = 678740) points to the data row identified by ().
Server: Msg 8952, Level 16, State 1, Line 1
Table error: Database 'db1', index 'F_Pm_Entry.PR_SEQ' (ID 1205579333) (index ID 2). Extra or invalid key for the keys:
Server: Msg 8956, Level 16, State 1, Line 1
Index row (1:66365:251) with values (PR_SEQ = 0 and PME_SEQ = 678741) points to the data row identified by ().
Server: Msg 8952, Level 16, State 1, Line 1
Table error: Database 'db1', index 'F_Pm_Entry.PR_SEQ' (ID 1205579333) (index ID 2). Extra or invalid key for the keys:
Server: Msg 8956, Level 16, State 1, Line 1
Index row (1:66365:252) with values (PR_SEQ = 0 and PME_SEQ = 678744) points to the data row identified by ().
Server: Msg 8952, Level 16, State 1, Line 1
Table error: Database 'db1', index 'F_Pm_Entry.PR_SEQ' (ID 1205579333) (index ID 2). Extra or invalid key for the keys:
Server: Msg 8956, Level 16, State 1, Line 1
Index row (1:66365:253) with values (PR_SEQ = 0 and PME_SEQ = 678745) points to the data row identified by ().
Server: Msg 8952, Level 16, State 1, Line 1
Table error: Database 'db1', index 'F_Pm_Entry.PR_SEQ' (ID 1205579333) (index ID 2). Extra or invalid key for the keys:
Server: Msg 8956, Level 16, State 1, Line 1
Index row (1:66365:254) with values (PR_SEQ = 0 and PME_SEQ = 678747) points to the data row identified by ().
Server: Msg 8952, Level 16, State 1, Line 1
Table error: Database 'db1', index 'F_Pm_Entry.PR_SEQ' (ID 1205579333) (index ID 2). Extra or invalid key for the keys:
Server: Msg 8956, Level 16, State 1, Line 1
Index row (1:66365:255) with values (PR_SEQ = 0 and PME_SEQ = 678748) points to the data row identified by ().
Server: Msg 8952, Level 16, State 1, Line 1
Table error: Database 'db1', index 'F_Pm_Entry.PR_SEQ' (ID 1205579333) (index ID 2). Extra or invalid key for the keys:
Server: Msg 8956, Level 16, State 1, Line 1
Index row (1:66365:256) with values (PR_SEQ = 0 and PME_SEQ = 678750) points to the data row identified by ().
Server: Msg 8952, Level 16, State 1, Line 1
Table error: Database 'db1', index 'F_Pm_Entry.PR_SEQ' (ID 1205579333) (index ID 2). Extra or invalid key for the keys:
Server: Msg 8956, Level 16, State 1, Line 1
Index row (1:66365:257) with values (PR_SEQ = 0 and PME_SEQ = 678751) points to the data row identified by ().
Server: Msg 8952, Level 16, State 1, Line 1
Table error: Database 'db1', index 'F_Pm_Entry.PR_SEQ' (ID 1205579333) (index ID 2). Extra or invalid key for the keys:

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2007-05-08 : 17:59:26

Are there any other errors referencing index ID 1? Can you email me the complete output of

DBCC CHECKDB (dbname) WITH ALL_ERRORMSGS, NO_INFOMSGS

Paul Randal
Principal Lead Program Manager, Microsoft SQL Server Core Storage Engine
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)
http://blogs.msdn.com/sqlserverstorageengine/default.aspx

advsteve
Starting Member

8 Posts

Posted - 2007-05-08 : 20:43:25

Sent it to you Paul

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2007-05-08 : 21:52:47

ok - how did you repair the original error? It looks like all the non-clustered indexes weren't rebuilt after the original bad page was removed.

Paul Randal
Principal Lead Program Manager, Microsoft SQL Server Core Storage Engine
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)
http://blogs.msdn.com/sqlserverstorageengine/default.aspx

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2007-05-08 : 21:53:14

(Hit send too soon)

Which CHECKDB should have done for you automatically.

Paul Randal
Principal Lead Program Manager, Microsoft SQL Server Core Storage Engine
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)
http://blogs.msdn.com/sqlserverstorageengine/default.aspx

advsteve
Starting Member

8 Posts

Posted - 2007-05-08 : 22:13:40

Used the repair_allow_data_loss
Anything I can do now?

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2007-05-09 : 14:14:39

Do you have the output from that run? I'd like to see what CHECKDB said it was going to do.

You can manually rebuild all seven non-clustered indexes on that table - that will fix them up.

Paul Randal
Principal Lead Program Manager, Microsoft SQL Server Core Storage Engine
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)
http://blogs.msdn.com/sqlserverstorageengine/default.aspx

advsteve
Starting Member

8 Posts

Posted - 2007-05-10 : 06:17:24

Unfortunately I didn't keep the output of what happened. I decided to go back to a backup before that window of opportunity was completely gone - it's a few days of re-input but at least I know the database is stable.. hopefully it won't happen again.

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2007-05-10 : 06:24:18

ok - one last thing for you to do is check all the firmware/driver versions to make sure they're all up-to-date. You may also want to run whatever diagnostics exist for your IO subsystem too.

Paul Randal
Principal Lead Program Manager, Microsoft SQL Server Core Storage Engine
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)
http://blogs.msdn.com/sqlserverstorageengine/default.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources