June 20, 2014 at 9:00 am
In a specific database
using the command:
SELECT count(*) FROM B
We got an error:
Msg 824, Level 24, State 2, Line 3
SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:319673; actual 0:0). It occurred during a read of page (1:319673) in database ID 11 at offset 0x0000009c172000 in file 'D:\SQL_Data\CDR610P13_SVZ14Q1_DBC610_Conversion_2.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
Using select * into commands the data was copied to a fresh created database.
Same result.
Create a Heap table, did get a 'correct' working heap table.
Adding a Clustered index to the heap table generated a similar error.
Adding a Clustered index to the heap table with a fillfactor = 50 did not generate a similar error.
The behavior does reproduce. Copying the data into the clustered table (no fillfactor) everytime produced the error on the count(*) statement.
Everytime adding the cluster to a heap (no fillfactor) produced an error.
Everytime adding the cluster to a heap (fillfactor = 50 ) produces a workable result.
Is this a known issue ?
Are we doing something wrong?
Ben
The datalength distribution of 16362725 Rows:
--number_off_rowsADDED_DATALENGT_ROW
--758638974
--82951873
--107963372
--240073270
--25901469
--40729868
--138522266
--1670965
--3137864
--76874162
--4073560
--144109858
--11622054
--3850
Running:
DBCC CHECKDB WITH ALL_ERRORMSGS, NO_INFOMSGS
Gives on the with fillfactor = 50 situation:
Command(s) completed successfully.
Running it on the other situation gives a number of errors.
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (1:319673) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (1:319674) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
Msg 8909, Level 16, State 1, Line 1
.......................................... etc.
This behavior is consistently.
June 20, 2014 at 9:15 am
Additional information.
The Showcontig information from both situations is very simular.
dbcc showcontig('B')
/*
-- FROM AN ERROR SITUATION :
DBCC SHOWCONTIG scanning 'B' table...
Table: 'BIL_Performed_Activities' (1157579162); index ID: 1, database ID: 11
TABLE level scan performed.
- Pages Scanned................................: 220290
- Extents Scanned..............................: 27667
- Extent Switches..............................: 27676
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.49% [27537:27677]
- Logical Scan Fragmentation ..................: 0.48%
- Extent Scan Fragmentation ...................: 0.02%
- Avg. Bytes Free per Page.....................: 61.8
- Avg. Page Density (full).....................: 99.24%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
-- FROM THE FILLFACTOR = 50 SITUATION (No errors)
DBCC SHOWCONTIG scanning 'B' table...
Table: 'BIL_Performed_Activities' (1157579162); index ID: 1, database ID: 13
TABLE level scan performed.
- Pages Scanned................................: 220290
- Extents Scanned..............................: 27671
- Extent Switches..............................: 27670
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.52% [27537:27671]
- Logical Scan Fragmentation ..................: 0.47%
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 61.8
- Avg. Page Density (full).....................: 99.24%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
*/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply