Consistency Errors - Large Table

I have a large table with 10 million records. It has only one clustered
index (0% fill factor) Total size of database is about 15 GB.
I am always running into consistency errors. It's frustating. Every two
days or so I run into a problem.
I have switched to another computer, and still getting errors, so I'm
not sure if it's a hardware problem or not.
Every day I run a query to update 3 million records in this table from
a flat file.
I get consistency errors often.
Object ID 802101898, index ID 0: Page (1:416734) could not be
processed. See other errors for details.
Server: Msg 8944, Level 16, State 1, Line 1
Table error: Object ID 802101898, index ID 0, page (1:416734), row 8.
Test (ColumnOffsets <= (nextRec - pRec)) failed. Values are 2477 and
535.
So I run CHECKDB with repair errors but that doesn't always help. So I
have to restore from the latest backup.
Could someone shed some light on this problem?You should contact Product Support to help you. Most likely its hardware
errors. When you switched to anothe computer, did you move the database to a
new IO subsystem and make sure it was clean of all consistency errors?
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
http://blogs.msdn.com/sqlserverstor...ne/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
<simone_maynard@.hotmail.com> wrote in message
news:1152928287.932830.124060@.h48g2000cwc.googlegroups.com...
>I have a large table with 10 million records. It has only one clustered
> index (0% fill factor) Total size of database is about 15 GB.
> I am always running into consistency errors. It's frustating. Every two
> days or so I run into a problem.
> I have switched to another computer, and still getting errors, so I'm
> not sure if it's a hardware problem or not.
> Every day I run a query to update 3 million records in this table from
> a flat file.
> I get consistency errors often.
> Object ID 802101898, index ID 0: Page (1:416734) could not be
> processed. See other errors for details.
> Server: Msg 8944, Level 16, State 1, Line 1
> Table error: Object ID 802101898, index ID 0, page (1:416734), row 8.
> Test (ColumnOffsets <= (nextRec - pRec)) failed. Values are 2477 and
> 535.
> So I run CHECKDB with repair errors but that doesn't always help. So I
> have to restore from the latest backup.
> Could someone shed some light on this problem?
>|||Version of SQLServer including SPs? OS and SPs? SQL? DDL for table?
J.
On 14 Jul 2006 18:51:27 -0700, simone_maynard@.hotmail.com wrote:
>I have a large table with 10 million records. It has only one clustered
>index (0% fill factor) Total size of database is about 15 GB.
>I am always running into consistency errors. It's frustating. Every two
>days or so I run into a problem.
>I have switched to another computer, and still getting errors, so I'm
>not sure if it's a hardware problem or not.
>Every day I run a query to update 3 million records in this table from
>a flat file.
>I get consistency errors often.
>Object ID 802101898, index ID 0: Page (1:416734) could not be
>processed. See other errors for details.
>Server: Msg 8944, Level 16, State 1, Line 1
>Table error: Object ID 802101898, index ID 0, page (1:416734), row 8.
>Test (ColumnOffsets <= (nextRec - pRec)) failed. Values are 2477 and
>535.
>So I run CHECKDB with repair errors but that doesn't always help. So I
>have to restore from the latest backup.
>Could someone shed some light on this problem?

Consistency Errors - Large Table

I have a large table with 10 million records. It has only one clustered
index (0% fill factor) Total size of database is about 15 GB.
I am always running into consistency errors. It's frustating. Every two
days or so I run into a problem.
I have switched to another computer, and still getting errors, so I'm
not sure if it's a hardware problem or not.
Every day I run a query to update 3 million records in this table from
a flat file.
I get consistency errors often.
Object ID 802101898, index ID 0: Page (1:416734) could not be
processed. See other errors for details.
Server: Msg 8944, Level 16, State 1, Line 1
Table error: Object ID 802101898, index ID 0, page (1:416734), row 8.
Test (ColumnOffsets <= (nextRec - pRec)) failed. Values are 2477 and
535.
So I run CHECKDB with repair errors but that doesn't always help. So I
have to restore from the latest backup.
Could someone shed some light on this problem?You should contact Product Support to help you. Most likely its hardware
errors. When you switched to anothe computer, did you move the database to a
new IO subsystem and make sure it was clean of all consistency errors?
--
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
<simone_maynard@.hotmail.com> wrote in message
news:1152928287.932830.124060@.h48g2000cwc.googlegroups.com...
>I have a large table with 10 million records. It has only one clustered
> index (0% fill factor) Total size of database is about 15 GB.
> I am always running into consistency errors. It's frustating. Every two
> days or so I run into a problem.
> I have switched to another computer, and still getting errors, so I'm
> not sure if it's a hardware problem or not.
> Every day I run a query to update 3 million records in this table from
> a flat file.
> I get consistency errors often.
> Object ID 802101898, index ID 0: Page (1:416734) could not be
> processed. See other errors for details.
> Server: Msg 8944, Level 16, State 1, Line 1
> Table error: Object ID 802101898, index ID 0, page (1:416734), row 8.
> Test (ColumnOffsets <= (nextRec - pRec)) failed. Values are 2477 and
> 535.
> So I run CHECKDB with repair errors but that doesn't always help. So I
> have to restore from the latest backup.
> Could someone shed some light on this problem?
>|||Version of SQLServer including SPs? OS and SPs? SQL? DDL for table?
J.
On 14 Jul 2006 18:51:27 -0700, simone_maynard@.hotmail.com wrote:
>I have a large table with 10 million records. It has only one clustered
>index (0% fill factor) Total size of database is about 15 GB.
>I am always running into consistency errors. It's frustating. Every two
>days or so I run into a problem.
>I have switched to another computer, and still getting errors, so I'm
>not sure if it's a hardware problem or not.
>Every day I run a query to update 3 million records in this table from
>a flat file.
>I get consistency errors often.
>Object ID 802101898, index ID 0: Page (1:416734) could not be
>processed. See other errors for details.
>Server: Msg 8944, Level 16, State 1, Line 1
>Table error: Object ID 802101898, index ID 0, page (1:416734), row 8.
>Test (ColumnOffsets <= (nextRec - pRec)) failed. Values are 2477 and
>535.
>So I run CHECKDB with repair errors but that doesn't always help. So I
>have to restore from the latest backup.
>Could someone shed some light on this problem?

ConnectionCheckForData Error on Delete

Hi,
I'm trying to delete 1.5 million records from at table containing 121
million records using a delete with a simple subquery. After some time
processing the following error message is returned in Query Analyzer:
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData
(CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broken
The system is Windows 2000 Server (SP4) and SQL Server 2000 EE (SP3 -
8.00.818).
I did some checking on the net and saw where an install of SP3a fixed a
related error message however the KB states that because most of the issues
are related to setup that SP3a need not be installed if SP3 is currently
installed.
Has anyone encountered this problem and if so how did you resolve it?
Thanks
JerryFollow up:
Ran the delete statement directly from QA on the SQL Server to remove the
network connectivity as a possible issue. Received the following error:
Server: Msg 8646, Level 21, State 1, Line 1
The index entry for row ID was not found in index ID 2, of table 1403152044,
in database 'XXX'.
Connection Broken
Referenced to KB Article 822747 - possible corrupt nonclustered index.
Dropped the NC index and the deleted now succeeded. Now rebuilding the NC
index again.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:eC3SJlWXFHA.3188@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I'm trying to delete 1.5 million records from at table containing 121
> million records using a delete with a simple subquery. After some time
> processing the following error message is returned in Query Analyzer:
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData
> (CheckforData()).
> Server: Msg 11, Level 16, State 1, Line 0
> General network error. Check your network documentation.
> Connection Broken
> The system is Windows 2000 Server (SP4) and SQL Server 2000 EE (SP3 -
> 8.00.818).
> I did some checking on the net and saw where an install of SP3a fixed a
> related error message however the KB states that because most of the
> issues are related to setup that SP3a need not be installed if SP3 is
> currently installed.
> Has anyone encountered this problem and if so how did you resolve it?
> Thanks
> Jerry
>

ConnectionCheckForData Error on Delete

Hi,
I'm trying to delete 1.5 million records from at table containing 121
million records using a delete with a simple subquery. After some time
processing the following error message is returned in Query Analyzer:
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForD
ata
(CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broken
The system is Windows 2000 Server (SP4) and SQL Server 2000 EE (SP3 -
8.00.818).
I did some checking on the net and saw where an install of SP3a fixed a
related error message however the KB states that because most of the issues
are related to setup that SP3a need not be installed if SP3 is currently
installed.
Has anyone encountered this problem and if so how did you resolve it?
Thanks
JerryFollow up:
Ran the delete statement directly from QA on the SQL Server to remove the
network connectivity as a possible issue. Received the following error:
Server: Msg 8646, Level 21, State 1, Line 1
The index entry for row ID was not found in index ID 2, of table 1403152044,
in database 'XXX'.
Connection Broken
Referenced to KB Article 822747 - possible corrupt nonclustered index.
Dropped the NC index and the deleted now succeeded. Now rebuilding the NC
index again.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:eC3SJlWXFHA.3188@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I'm trying to delete 1.5 million records from at table containing 121
> million records using a delete with a simple subquery. After some time
> processing the following error message is returned in Query Analyzer:
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckFo
rData
> (CheckforData()).
> Server: Msg 11, Level 16, State 1, Line 0
> General network error. Check your network documentation.
> Connection Broken
> The system is Windows 2000 Server (SP4) and SQL Server 2000 EE (SP3 -
> 8.00.818).
> I did some checking on the net and saw where an install of SP3a fixed a
> related error message however the KB states that because most of the
> issues are related to setup that SP3a need not be installed if SP3 is
> currently installed.
> Has anyone encountered this problem and if so how did you resolve it?
> Thanks
> Jerry
>

ConnectionCheckForData Error on Delete

Hi,
I'm trying to delete 1.5 million records from at table containing 121
million records using a delete with a simple subquery. After some time
processing the following error message is returned in Query Analyzer:
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData
(CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broken
The system is Windows 2000 Server (SP4) and SQL Server 2000 EE (SP3 -
8.00.818).
I did some checking on the net and saw where an install of SP3a fixed a
related error message however the KB states that because most of the issues
are related to setup that SP3a need not be installed if SP3 is currently
installed.
Has anyone encountered this problem and if so how did you resolve it?
Thanks
Jerry
Follow up:
Ran the delete statement directly from QA on the SQL Server to remove the
network connectivity as a possible issue. Received the following error:
Server: Msg 8646, Level 21, State 1, Line 1
The index entry for row ID was not found in index ID 2, of table 1403152044,
in database 'XXX'.
Connection Broken
Referenced to KB Article 822747 - possible corrupt nonclustered index.
Dropped the NC index and the deleted now succeeded. Now rebuilding the NC
index again.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:eC3SJlWXFHA.3188@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I'm trying to delete 1.5 million records from at table containing 121
> million records using a delete with a simple subquery. After some time
> processing the following error message is returned in Query Analyzer:
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData
> (CheckforData()).
> Server: Msg 11, Level 16, State 1, Line 0
> General network error. Check your network documentation.
> Connection Broken
> The system is Windows 2000 Server (SP4) and SQL Server 2000 EE (SP3 -
> 8.00.818).
> I did some checking on the net and saw where an install of SP3a fixed a
> related error message however the KB states that because most of the
> issues are related to setup that SP3a need not be installed if SP3 is
> currently installed.
> Has anyone encountered this problem and if so how did you resolve it?
> Thanks
> Jerry
>

connection timeout

Hi there
I'm using SQL Server 2000. I often use the UPDATE ... SET ... statements to
change data in my database, affecting up to 3 million rows of data. If I
issue the statements from Enterprise Manager I get a "timeout expired" error
on large queries, whereas if I execute the queries from Query Analyser, they
perform fine if very slowly. Mostly at the moment I'm happy that they
execute.
Now, if I issue the statements over an ADO connection from Microsoft Excel
(using a VBA procedure, with the statements issued either on-the-fly or via
a stored query), I get the same "timeout expired" error.
Any ideas?
Best regards
Loane
Hi
Make sure that your database and log file have been grown big enough before
you run the update.
Make sure that the Where clause column is indexed.
If possible, break up your update into smaller pieces.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Loane Sharp" <look_sharp_not@.hotmail.com> wrote in message
news:u$mMC4GZFHA.228@.TK2MSFTNGP12.phx.gbl...
> Hi there
> I'm using SQL Server 2000. I often use the UPDATE ... SET ... statements
> to change data in my database, affecting up to 3 million rows of data. If
> I issue the statements from Enterprise Manager I get a "timeout expired"
> error on large queries, whereas if I execute the queries from Query
> Analyser, they perform fine if very slowly. Mostly at the moment I'm happy
> that they execute.
> Now, if I issue the statements over an ADO connection from Microsoft Excel
> (using a VBA procedure, with the statements issued either on-the-fly or
> via a stored query), I get the same "timeout expired" error.
> Any ideas?
> Best regards
> Loane
>
|||That is because EM has a default timeout for connections at 30 seconds as do
most drivers. QA on the other hand defaults to 0 which means it never times
out. When ever possible try to avoid doing operations on so many rows all
in one transaction. Most operations such as that can sit in a loop and do
the updates in smaller batches of say 10 or 20 thousand rows at a time.
That way you should never timeout and won't block other users as much or for
as long a period of time. There are plenty of ways to approach a situation
such as that. If you need help then post the DDL for the table and the
update statement you normally would use.
Andrew J. Kelly SQL MVP
"Loane Sharp" <look_sharp_not@.hotmail.com> wrote in message
news:u$mMC4GZFHA.228@.TK2MSFTNGP12.phx.gbl...
> Hi there
> I'm using SQL Server 2000. I often use the UPDATE ... SET ... statements
> to change data in my database, affecting up to 3 million rows of data. If
> I issue the statements from Enterprise Manager I get a "timeout expired"
> error on large queries, whereas if I execute the queries from Query
> Analyser, they perform fine if very slowly. Mostly at the moment I'm happy
> that they execute.
> Now, if I issue the statements over an ADO connection from Microsoft Excel
> (using a VBA procedure, with the statements issued either on-the-fly or
> via a stored query), I get the same "timeout expired" error.
> Any ideas?
> Best regards
> Loane
>

connection timeout

Hi there
I'm using SQL Server 2000. I often use the UPDATE ... SET ... statements to
change data in my database, affecting up to 3 million rows of data. If I
issue the statements from Enterprise Manager I get a "timeout expired" error
on large queries, whereas if I execute the queries from Query Analyser, they
perform fine if very slowly. Mostly at the moment I'm happy that they
execute.
Now, if I issue the statements over an ADO connection from Microsoft Excel
(using a VBA procedure, with the statements issued either on-the-fly or via
a stored query), I get the same "timeout expired" error.
Any ideas?
Best regards
LoaneHi
Make sure that your database and log file have been grown big enough before
you run the update.
Make sure that the Where clause column is indexed.
If possible, break up your update into smaller pieces.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Loane Sharp" <look_sharp_not@.hotmail.com> wrote in message
news:u$mMC4GZFHA.228@.TK2MSFTNGP12.phx.gbl...
> Hi there
> I'm using SQL Server 2000. I often use the UPDATE ... SET ... statements
> to change data in my database, affecting up to 3 million rows of data. If
> I issue the statements from Enterprise Manager I get a "timeout expired"
> error on large queries, whereas if I execute the queries from Query
> Analyser, they perform fine if very slowly. Mostly at the moment I'm happy
> that they execute.
> Now, if I issue the statements over an ADO connection from Microsoft Excel
> (using a VBA procedure, with the statements issued either on-the-fly or
> via a stored query), I get the same "timeout expired" error.
> Any ideas?
> Best regards
> Loane
>|||That is because EM has a default timeout for connections at 30 seconds as do
most drivers. QA on the other hand defaults to 0 which means it never times
out. When ever possible try to avoid doing operations on so many rows all
in one transaction. Most operations such as that can sit in a loop and do
the updates in smaller batches of say 10 or 20 thousand rows at a time.
That way you should never timeout and won't block other users as much or for
as long a period of time. There are plenty of ways to approach a situation
such as that. If you need help then post the DDL for the table and the
update statement you normally would use.
Andrew J. Kelly SQL MVP
"Loane Sharp" <look_sharp_not@.hotmail.com> wrote in message
news:u$mMC4GZFHA.228@.TK2MSFTNGP12.phx.gbl...
> Hi there
> I'm using SQL Server 2000. I often use the UPDATE ... SET ... statements
> to change data in my database, affecting up to 3 million rows of data. If
> I issue the statements from Enterprise Manager I get a "timeout expired"
> error on large queries, whereas if I execute the queries from Query
> Analyser, they perform fine if very slowly. Mostly at the moment I'm happy
> that they execute.
> Now, if I issue the statements over an ADO connection from Microsoft Excel
> (using a VBA procedure, with the statements issued either on-the-fly or
> via a stored query), I get the same "timeout expired" error.
> Any ideas?
> Best regards
> Loane
>