Index corruption on table without key: what could be the reasons ?

Dear group,

we are running a SQL-Server Database which is about 30 GB large. The
purpose of this database is to contain periodic data from automatic
devices which insert values into some tables.

Unfortunately most of these tables don't have a key (and a key can only
be introduced when the application programmers have changed their
software). Tables have this structure

deviceno timestamp data

where we expect for every device and timestamp one row of data.

In the ongoing operation it happens that the index of this large table
gets corrupted and a select from this table yields 2 rows for some
devices.

In fact a select "SELECT DEVICENO, TIMESTAMP, COUNT(*) FROM TABLE GROUP
BY DEVICENO, TIMESTAMP HAVING COUNT(*) > 1" returns lots of data.

After rebuild of the indexes the table is "clean" again.

What could cause the index corruption ?

Missing key?
Faulty application program ?
a combination of both ?

How can i prevent this from happening again, as long as there is no
updated database / application ?

I'd be grateful for any useful comment

Regards

UliJust to clarify a common misconception: key constraints are used to
enfore data integrity, and indexes are used to enhance performance.
While primary keys also introduce an index upon creation, they are not
the same thing; you can create indexes without having primary keys at
all (this is a technique that can be used in data warehouses, where the
normalization rules are a bit "looser"). Index fragmentation is
unrelated to the presence or absence of keys; it can be, however,
related to the underlying clustered index that is (by default)
associated with the primary key.

A couple of questions: do you have a clustered index on this table?
On what column is it located?

Do you know how to use the DBCC command SHOWCONTIG? It will help you
identify it is fragmentation, and where it is occurring.

http://www.sql-server-performance.c..._showcontig.asp

HTH,
Stu|||Hi

You do not say what the index is on, and whether it is a unique index.

You may want to read:
http://www.aspfaq.com/show.asp?id=2081
http://www.aspfaq.com/etiquette.asp?id=5006

John

<uli2003wien@.lycos.at> wrote in message
news:1126693834.052381.263790@.g44g2000cwa.googlegr oups.com...
> Dear group,
> we are running a SQL-Server Database which is about 30 GB large. The
> purpose of this database is to contain periodic data from automatic
> devices which insert values into some tables.
> Unfortunately most of these tables don't have a key (and a key can only
> be introduced when the application programmers have changed their
> software). Tables have this structure
> deviceno timestamp data
> where we expect for every device and timestamp one row of data.
> In the ongoing operation it happens that the index of this large table
> gets corrupted and a select from this table yields 2 rows for some
> devices.
> In fact a select "SELECT DEVICENO, TIMESTAMP, COUNT(*) FROM TABLE GROUP
> BY DEVICENO, TIMESTAMP HAVING COUNT(*) > 1" returns lots of data.
> After rebuild of the indexes the table is "clean" again.
> What could cause the index corruption ?
> Missing key?
> Faulty application program ?
> a combination of both ?
> How can i prevent this from happening again, as long as there is no
> updated database / application ?
> I'd be grateful for any useful comment
> Regards
> Uli|||(uli2003wien@.lycos.at) writes:
> we are running a SQL-Server Database which is about 30 GB large. The
> purpose of this database is to contain periodic data from automatic
> devices which insert values into some tables.
> Unfortunately most of these tables don't have a key (and a key can only
> be introduced when the application programmers have changed their
> software). Tables have this structure
> deviceno timestamp data
> where we expect for every device and timestamp one row of data.
> In the ongoing operation it happens that the index of this large table
> gets corrupted and a select from this table yields 2 rows for some
> devices.
> In fact a select "SELECT DEVICENO, TIMESTAMP, COUNT(*) FROM TABLE GROUP
> BY DEVICENO, TIMESTAMP HAVING COUNT(*) > 1" returns lots of data.
> After rebuild of the indexes the table is "clean" again.
> What could cause the index corruption ?
> Missing key?
> Faulty application program ?
> a combination of both ?

If the duplicates disappear after a DBCC DBREINDEX (or DROP + CREATE, then
it is the index that is corrupted.

I seem to recall that there is an issue with heap tables that could cause
this. (A heap table is a table that does not have a clustered index.)
Can you define the index as clustered? Even better if you can add UNIQUE to
enforce uniqueness. Then again, it sounds as if the application is able
to insert duplicates?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||dont ever make a single table in your whole life that doesn't have a
single-column identity primary key|||Please tell me you're joking.

Stu|||<uli2003wien@.lycos.at> wrote:

> Dear group,
> we are running a SQL-Server Database which is about 30 GB large. The
> purpose of this database is to contain periodic data from automatic
> devices which insert values into some tables.
> Unfortunately most of these tables don't have a key (and a key can only
> be introduced when the application programmers have changed their
> software). Tables have this structure
> deviceno timestamp data
> where we expect for every device and timestamp one row of data.
> In the ongoing operation it happens that the index of this large table
> gets corrupted and a select from this table yields 2 rows for some
> devices.
> In fact a select "SELECT DEVICENO, TIMESTAMP, COUNT(*) FROM TABLE GROUP
> BY DEVICENO, TIMESTAMP HAVING COUNT(*) > 1" returns lots of data.
> After rebuild of the indexes the table is "clean" again.
> What could cause the index corruption ?
> Missing key?
> Faulty application program ?
> a combination of both ?
> How can i prevent this from happening again, as long as there is no
> updated database / application ?
> I'd be grateful for any useful comment
> Regards
> Uli

Uli,

Depending on how often your devices are firing inserts and how the timestamp
column is defined, I would expect to find multiple rows for a single
deviceno and timestamp combination (especially if you're using
smalldatetime).

But if I understand you correctly, you drop and re-create an index (or use
DBCC REINDEX) and the SELECT you posted returns different results (and this
happens consistently). If that's the case, you need to call MS support:
that's not supposed to happen.

Craig|||<dbahooker@.hotmail.com> wrote in message
news:1127919145.122247.19780@.g49g2000cwa.googlegro ups.com...
> dont ever make a single table in your whole life that doesn't have a
> single-column identity primary key

This is amazingly poor advice.
|||why?

aren't all indexes slower if you have your clustered index on a
multi-column varchar field?

i mean.. INT IDENTITY EVERYWHERE|||no greg im serious

why is it bad advice?

i just dont believe in tables that dont have a single numeric primary
key.

I'm just too friggin tired to use 'A345FD' as a primary key anymore..
use INTs or BIGINTs if you want. i would rather use a bigint than a
varchar any day of the week

but.. i mean.. no keys?

are you drunk?|||(dbahooker@.hotmail.com) writes:
> no greg im serious
> why is it bad advice?
> i just dont believe in tables that dont have a single numeric primary
> key.
> I'm just too friggin tired to use 'A345FD' as a primary key anymore..
> use INTs or BIGINTs if you want. i would rather use a bigint than a
> varchar any day of the week
> but.. i mean.. no keys?
> are you drunk?

Greg is usually not drunk when he posts. In fact, I have never felt
any smell of liquor from his posts.

Having a one-column key in all tables is actually a really poor idea.
Typical example: an order has a one-column key, which is an orderid.
But a detail row on a order has a two column key: an orderid and a
row number (or a product id).

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||<dbahooker@.hotmail.com> wrote in message
news:1129052916.884917.318460@.o13g2000cwo.googlegr oups.com...
> no greg im serious
> why is it bad advice?

Because it doesn't necessarily model reality.

> i just dont believe in tables that dont have a single numeric primary
> key.

So if you were designing a database of cars you'd create your own single
numeric primary key rather than VIN?

Or if you were designing a database for Books you'd eschew the ISBN and use
an Identity column?

> I'm just too friggin tired to use 'A345FD' as a primary key anymore..

Then I suggest you get some more sleep.

> use INTs or BIGINTs if you want. i would rather use a bigint than a
> varchar any day of the week
> but.. i mean.. no keys?

Who said anything about no keys. Certainly not I. It's not a table w/o
keys.

> are you drunk?

Not hardly.
|||Erland Sommarskog wrote:
> Having a one-column key in all tables is actually a really poor idea.
> Typical example: an order has a one-column key, which is an orderid.
> But a detail row on a order has a two column key: an orderid and a
> row number (or a product id).

A problem arises when you have more than a simple system. You have:

Project (ProjectNo)
Order (ProjectNo, OrderNo)
Items (ProjectNo, OrderNo, ItemNo)
Expediting Split components [1] (ProjectNo, OrderNo, ItemNo, CompSplit)
Expediting Split Deliveries (ProjectNo, OrderNo, ItemNo, CompSplit,
DelSplit)
In real world, expeditor has no idea that freight forwarder can't fit
1,000,000 bricks on one truck (don't laff, I've seen it happen) so MMTs
(Material Movement Ticket) are split (ProjectNo, OrderNo, ItemNo,
CompSplit, DelSplit, MMTNo)
The MMT may go onto a container, then later on is split into trucks
(that move at different speeds), or some items get held longer in
customs than others resulting in many MRRs (Material Received Report)
for one MMT, MMR Item has a key that looks like a complete table before
any meaningful data gets there.

Now, given that having 7 columns in a PK is stupid, do you:

a) Give a surrogate key to some tables and a natural key to others and
thereby be inconsistent or

b) Be consistent and use surrogate keys?

Given that no-one in their right mind would use just a surrogate key as
the unique constraint but also have a candidate key so no real
duplicates can arise. Also that these surrogate keys (Identity columns)
are *never* presented to the user, only the computer knows they exist.

[1] e.g. you order 1 x lifeboat but supplier later advises split
delivery of 1 x dingy, 6 x oars, 6 x life jackets, 1 x motor, etc.|||First, your advice indicates that you don't understand the difference
between an index and a key; since you seem to have a background in
Access programming, that's understandable because Access goes out of
its way to hide the distinction. However, in SQL Server, indexes are
very different than keys, with different purposes. A key is used to
establish and maintain data integrity; an index is used to expedite
data retrieval.

Second, (as others have pointed out) a surrogate key has problems. It
doesn't reflect reality and it cannot be used for data validation.
However, I will grant you that using a simple surrogate key (like an
integer) can have benefits in terms of performance, scalability, and
data maintenance; however, those benefits come with a cost.

Which leads to my third point: by specifying an absolute, you have
pigeonholed your designs. It's a bit like a mechanic insisting on
using a #2 Phillips head bit on a #3 hole; all you get is a stripped
out, screwed up design. Again, using surrogate keys with a clustered
index is NOT a bad idea in the right situation; but in the wrong
situation, it's a dumb idea.

Stu|||Trevor Best (nospam@.localhost.invalid) writes:
> Erland Sommarskog wrote:
>> Having a one-column key in all tables is actually a really poor idea.
>> Typical example: an order has a one-column key, which is an orderid.
>> But a detail row on a order has a two column key: an orderid and a
>> row number (or a product id).
> A problem arises when you have more than a simple system. You have:

Dunno. With something like 500 tables in the data model I maintain, I
guess that it qualifies as "more than a simple system". And, no, not
all of them have one-column keys.

> Project (ProjectNo)
> Order (ProjectNo, OrderNo)
> Items (ProjectNo, OrderNo, ItemNo)
> Expediting Split components [1] (ProjectNo, OrderNo, ItemNo, CompSplit)
> Expediting Split Deliveries (ProjectNo, OrderNo, ItemNo, CompSplit,
> DelSplit)
> In real world, expeditor has no idea that freight forwarder can't fit
> 1,000,000 bricks on one truck (don't laff, I've seen it happen) so MMTs
> (Material Movement Ticket) are split (ProjectNo, OrderNo, ItemNo,
> CompSplit, DelSplit, MMTNo)
> The MMT may go onto a container, then later on is split into trucks
> (that move at different speeds), or some items get held longer in
> customs than others resulting in many MRRs (Material Received Report)
> for one MMT, MMR Item has a key that looks like a complete table before
> any meaningful data gets there.
> Now, given that having 7 columns in a PK is stupid, do you:

I don't know the business domain well enough, so I prefer to not comment
on the details.

But I was once in the situation where I had a table with a four-column
key, and a need arose for a subtable with two more keys. So, I added
a surrogate to the existing table.

Much later I had reason to write code to maintain data in those tables,
and the UPDATE/INSERT for the subtable were complex. Because of the
surrogate key.

That surrogate key is no more by the way. (But for other reasons, two
other key levels have also gone away.)

On the top of my head I know we have a table with a five-column key. But
I would shudder for a seven-colunm key if it was the natural one.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

index corruption issues

We are having some troubles with corrupt indexes on our SQL 2000
dbase.

It is only affecting 2 tables out of about 150 we have.

These 2 tables are heavily used.

We have run dbcc repairs; dropped and recreated indexes but the
problem persists

Is it possible that this ongoing problem is indicative of a hardware
problem.

If so how could we diagnose this?

here is a sample of the dbcc check against one of the tables whose
indexes we are having trouble with:

thanks

J.C. Clay

Server: Msg 8951, Level 16, State 1, Line 1
Table error: Table 'page_views' (ID 1436024347). Missing or invalid
key in index 'IX_sessionid' (ID 7) for the row:
Server: Msg 8955, Level 16, State 1, Line 1
Data row (1:42151:27) identified by (RID = (1:42151:27) ) has index
values (session_id = B89F855E-CBB7-4874-A3BD-8D8827ED1481@._ ).
Server: Msg 8951, Level 16, State 1, Line 1
Table error: Table 'page_views' (ID 1436024347). Missing or invalid
key in index 'IX_page_views_nav' (ID 8) for the row:
Server: Msg 8955, Level 16, State 1, Line 1
Data row (1:42151:27) identified by (RID = (1:42151:27) ) has index
values (navigation_id = A9E17BF3-41A6-43EF-8FD6-15C5B5E5E744@._ ).
Server: Msg 8951, Level 16, State 1, Line 1
Table error: Table 'page_views' (ID 1436024347). Missing or invalid
key in index 'IX_page_views_tab_nav' (ID 10) for the row:
Server: Msg 8955, Level 16, State 1, Line 1
Data row (1:42151:27) identified by (RID = (1:42151:27) ) has index
values (tab_navigation_id =
6373D50E-EF0B-4084-B8A7-032653E262E2@._ ).
Server: Msg 8951, Level 16, State 1, Line 1
Table error: Table 'page_views' (ID 1436024347). Missing or invalid
key in index 'IX_page_view_id' (ID 11) for the row:
Server: Msg 8955, Level 16, State 1, Line 1
Data row (1:42151:27) identified by (RID = (1:42151:27) ) has index
values (page_view_id = 431E5427-A688-4957-967B-A3466799D94E@._ ).
DBCC results for 'page_views'.
There are 958422 rows in 54595 pages for object 'page_views'.
CHECKTABLE found 0 allocation errors and 4 consistency errors in table
'page_views' (object ID 1436024347).
repair_fast is the minimum repair level for the errors found by DBCC
CHECKTABLE (profound.dbo.page_views ).bigmuldoon@.yahoo.com (J. C. Clay) wrote in message news:<30005581.0307160848.68362af2@.posting.google.com>...
> We are having some troubles with corrupt indexes on our SQL 2000
> dbase.
> It is only affecting 2 tables out of about 150 we have.
> These 2 tables are heavily used.
> We have run dbcc repairs; dropped and recreated indexes but the
> problem persists
> Is it possible that this ongoing problem is indicative of a hardware
> problem.
> If so how could we diagnose this?
> here is a sample of the dbcc check against one of the tables whose
> indexes we are having trouble with:
> thanks
> J.C. Clay
> Server: Msg 8951, Level 16, State 1, Line 1
> Table error: Table 'page_views' (ID 1436024347). Missing or invalid
> key in index 'IX_sessionid' (ID 7) for the row:
> Server: Msg 8955, Level 16, State 1, Line 1
> Data row (1:42151:27) identified by (RID = (1:42151:27) ) has index
> values (session_id = B89F855E-CBB7-4874-A3BD-8D8827ED1481@._ ).
> Server: Msg 8951, Level 16, State 1, Line 1
> Table error: Table 'page_views' (ID 1436024347). Missing or invalid
> key in index 'IX_page_views_nav' (ID 8) for the row:
> Server: Msg 8955, Level 16, State 1, Line 1
> Data row (1:42151:27) identified by (RID = (1:42151:27) ) has index
> values (navigation_id = A9E17BF3-41A6-43EF-8FD6-15C5B5E5E744@._ ).
> Server: Msg 8951, Level 16, State 1, Line 1
> Table error: Table 'page_views' (ID 1436024347). Missing or invalid
> key in index 'IX_page_views_tab_nav' (ID 10) for the row:
> Server: Msg 8955, Level 16, State 1, Line 1
> Data row (1:42151:27) identified by (RID = (1:42151:27) ) has index
> values (tab_navigation_id =
> 6373D50E-EF0B-4084-B8A7-032653E262E2@._ ).
> Server: Msg 8951, Level 16, State 1, Line 1
> Table error: Table 'page_views' (ID 1436024347). Missing or invalid
> key in index 'IX_page_view_id' (ID 11) for the row:
> Server: Msg 8955, Level 16, State 1, Line 1
> Data row (1:42151:27) identified by (RID = (1:42151:27) ) has index
> values (page_view_id = 431E5427-A688-4957-967B-A3466799D94E@._ ).
> DBCC results for 'page_views'.
> There are 958422 rows in 54595 pages for object 'page_views'.
> CHECKTABLE found 0 allocation errors and 4 consistency errors in table
> 'page_views' (object ID 1436024347).
> repair_fast is the minimum repair level for the errors found by DBCC
> CHECKTABLE (profound.dbo.page_views ).

It looks like you're using uniqueidentifier values, so you might be
seeing this issue:

http://support.microsoft.com/defaul...kb;en-us;284440

Simon

index corruption ?

We have a problem with a result set returned from a query:
select number, version, division, account
from probill
where currentstatus in ( '0', 'B', 'D', 'J', 'R', 'A' )
and rebill = 1
order by number
Primary key on number, version ; non-clustered index on currentstatus,
non-clustered index on rebill.
Each row of the result set returned is duplicated, 306 rows.
If I reindex the table, the problem goes away temporarily. If I
execute : sp_configure 'max degree of parallelism', '1' and
reconfigure, I get the correct result set, 153 rows. Setting the
maxdop to '0' again and I'm back to 306 rows.
Any idea(s) what could cause this type of problem? Hardware? Corrupt
indexes? SQL server bug? All the hardware diagnostics check out fine.
Sounds like a bug with the parallel plan to me. Are you current on service pack? Have you checked
against KB?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<nospam15151@.mailinator.com> wrote in message news:chptbj$a9c@.odak26.prod.google.com...
> We have a problem with a result set returned from a query:
> select number, version, division, account
> from probill
> where currentstatus in ( '0', 'B', 'D', 'J', 'R', 'A' )
> and rebill = 1
> order by number
> Primary key on number, version ; non-clustered index on currentstatus,
> non-clustered index on rebill.
> Each row of the result set returned is duplicated, 306 rows.
> If I reindex the table, the problem goes away temporarily. If I
> execute : sp_configure 'max degree of parallelism', '1' and
> reconfigure, I get the correct result set, 153 rows. Setting the
> maxdop to '0' again and I'm back to 306 rows.
> Any idea(s) what could cause this type of problem? Hardware? Corrupt
> indexes? SQL server bug? All the hardware diagnostics check out fine.
>
|||<nospam15151@.mailinator.com> wrote in message
news:chptbj$a9c@.odak26.prod.google.com...
> If I reindex the table, the problem goes away temporarily. If I
> execute : sp_configure 'max degree of parallelism', '1' and
> reconfigure, I get the correct result set, 153 rows. Setting the
> maxdop to '0' again and I'm back to 306 rows.
> Any idea(s) what could cause this type of problem? Hardware? Corrupt
> indexes? SQL server bug? All the hardware diagnostics check out fine.
Sounds like a MAXDOP bug, since that fixes it... Are you running the
lastest Service Pack (3a)?
If so, you might want to check http://support.microsoft.com and see if
there's a hotfix available for what you're experiencing.
|||Hi,
We are running sp3a. I wasn't able to find anything specific in the
knowledge base. Q 330212 looked close though. That's what led me to
try changing the maxdop setting.

index corruption ?

We have a problem with a result set returned from a query:
select number, version, division, account
from probill
where currentstatus in ( '0', 'B', 'D', 'J', 'R', 'A' )
and rebill = 1
order by number
Primary key on number, version ; non-clustered index on currentstatus,
non-clustered index on rebill.
Each row of the result set returned is duplicated, 306 rows.
If I reindex the table, the problem goes away temporarily. If I
execute : sp_configure 'max degree of parallelism', '1' and
reconfigure, I get the correct result set, 153 rows. Setting the
maxdop to '0' again and I'm back to 306 rows.
Any idea(s) what could cause this type of problem? Hardware? Corrupt
indexes? SQL server bug? All the hardware diagnostics check out fine.Sounds like a bug with the parallel plan to me. Are you current on service pack? Have you checked
against KB?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<nospam15151@.mailinator.com> wrote in message news:chptbj$a9c@.odak26.prod.google.com...
> We have a problem with a result set returned from a query:
> select number, version, division, account
> from probill
> where currentstatus in ( '0', 'B', 'D', 'J', 'R', 'A' )
> and rebill = 1
> order by number
> Primary key on number, version ; non-clustered index on currentstatus,
> non-clustered index on rebill.
> Each row of the result set returned is duplicated, 306 rows.
> If I reindex the table, the problem goes away temporarily. If I
> execute : sp_configure 'max degree of parallelism', '1' and
> reconfigure, I get the correct result set, 153 rows. Setting the
> maxdop to '0' again and I'm back to 306 rows.
> Any idea(s) what could cause this type of problem? Hardware? Corrupt
> indexes? SQL server bug? All the hardware diagnostics check out fine.
>|||<nospam15151@.mailinator.com> wrote in message
news:chptbj$a9c@.odak26.prod.google.com...
> If I reindex the table, the problem goes away temporarily. If I
> execute : sp_configure 'max degree of parallelism', '1' and
> reconfigure, I get the correct result set, 153 rows. Setting the
> maxdop to '0' again and I'm back to 306 rows.
> Any idea(s) what could cause this type of problem? Hardware? Corrupt
> indexes? SQL server bug? All the hardware diagnostics check out fine.
Sounds like a MAXDOP bug, since that fixes it... Are you running the
lastest Service Pack (3a)?
If so, you might want to check http://support.microsoft.com and see if
there's a hotfix available for what you're experiencing.

Index Corruption

Hi,
What's the best approach to identify and fix index corruption problems. I
have some tables that corruption is occuring frequently.
Any idea ?
Vitor MauricioFrequent corruption is not normal. You would want to check
your hardware and the windows event logs for any indications
of hardware issues.
You can identify issues using dbcc checkdb. If you want to
limit the check to specific tables, you dbcc checktable.
The best approach to fixing the issues is to do root cause
analysis and determine what is causing the corruption and
then address those issues.
-Sue
On Mon, 18 Oct 2004 23:01:02 -0300, "Vitor Mauricio de N.
Silva" <vitor_mauricio@.newsgroup.nospam> wrote:
>Hi,
>What's the best approach to identify and fix index corruption problems. I
>have some tables that corruption is occuring frequently.
>Any idea ?
>Vitor Mauricio
>|||Hi Vitor Mauricio,
I wanted to post a quick note to see if you would like additional
assistance or information regarding the information Sue had sent to you. We
appreciate your patience and look forward to hearing from you!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Corruption could happen if you are performing a few operations on a
given table in batch without commiting them. Say for instance you
delete a bunch of records, and perform an insert, then update a field.
If you are doing this in a batch, then you could possibly run into a
corruption issue due to the fact that the datbase hasnt committed the
activity. The index "thinks" that there are more records there then
there actually are, so in this case the index becomes corrupt and you
must re-build it. One way around this is to separate your logic into
more compartmentalized code or update statistics between operations.
Vitor Mauricio de N. Silva wrote:
> *Hi,
> What's the best approach to identify and fix index corruption
> problems. I
> have some tables that corruption is occuring frequently.
> Any idea ?
> Vitor Mauricio *
mbroaddus
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message1165249.html

Index Corruption

Hi,
Does anyone know of an obvious reason for indexes to get corrupt or have the
statistics get really out of wack' We had a problem today where the
performance just went down the toilet.. It was really obvious that a
particular frequent query was taking 1000 times longer than normal.. The
query plan had a new start "Scan Constants'" I dropped and recreated
the index and the problem went away..
Why would it so suddenly go bad?
Thanks
BillHi
Auto Create and Update Statistics On?
The SP could generate another query plan during the day it it finds it's
earlier estimations were wrong.
I have found that once the statistics get really out of date, queries going
souith are occuring more frequently.
There has been a bit of discussion on this in
microsoft.public.sqlserver.server and
microsoft.public.sqlserver.programming lately on it.
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/
"Bill" <someone@.somewhere.com> wrote in message
news:u9uGUCkNFHA.688@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Does anyone know of an obvious reason for indexes to get corrupt or have
> the statistics get really out of wack' We had a problem today where
> the performance just went down the toilet.. It was really obvious that
> a particular frequent query was taking 1000 times longer than normal..
> The query plan had a new start "Scan Constants'" I dropped and
> recreated the index and the problem went away..
> Why would it so suddenly go bad?
> Thanks
> Bill
>|||Yep, Auto Create and Auto Update are on...
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:ujKN8FkNFHA.1396@.TK2MSFTNGP10.phx.gbl...
> Hi
> Auto Create and Update Statistics On?
> The SP could generate another query plan during the day it it finds it's
> earlier estimations were wrong.
> I have found that once the statistics get really out of date, queries
> going souith are occuring more frequently.
> There has been a bit of discussion on this in
> microsoft.public.sqlserver.server and
> microsoft.public.sqlserver.programming lately on it.
> 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/
> "Bill" <someone@.somewhere.com> wrote in message
> news:u9uGUCkNFHA.688@.TK2MSFTNGP10.phx.gbl...
>> Hi,
>> Does anyone know of an obvious reason for indexes to get corrupt or have
>> the statistics get really out of wack' We had a problem today where
>> the performance just went down the toilet.. It was really obvious that
>> a particular frequent query was taking 1000 times longer than normal..
>> The query plan had a new start "Scan Constants'" I dropped and
>> recreated the index and the problem went away..
>> Why would it so suddenly go bad?
>> Thanks
>> Bill
>|||That is not index corruption and by reindexing you simply forced a new plan
to be generated. You could have just recompiled the proc. This happens
occasionally. You can have a value that is atypical and requires a scan
where as all the others would use a seek. If the first time the proc was
compiled or recompiled it happened to get that bad value passed in the query
plan would be right for that one time but wrong for the majority of the
others.
--
Andrew J. Kelly SQL MVP
"Bill" <someone@.somewhere.com> wrote in message
news:u9uGUCkNFHA.688@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Does anyone know of an obvious reason for indexes to get corrupt or have
> the statistics get really out of wack' We had a problem today where
> the performance just went down the toilet.. It was really obvious that
> a particular frequent query was taking 1000 times longer than normal..
> The query plan had a new start "Scan Constants'" I dropped and
> recreated the index and the problem went away..
> Why would it so suddenly go bad?
> Thanks
> Bill
>

Index corruption

Hi all.
I've got a database that people are posting data to
through the index. The other day, someone posted a
series of records and called a select statment from the
index to check their work. When they did, three entries
were missing. I went back and found that the data was on
the disks and could be regenerated by recreating the
index tables.
Now I'm trying to figure out why this happened and
whether I can expect that it will happen again. I
restored the last .bak file prior to the incident and
walked through the transaction logs up to the event,
running checkdb with each. The first trans log after the
event reported that it could not be restored due to an ID
being newer on the previous trans log.
I've now restored the first .bak file after the incident
and run checkdb. I'm getting this error:
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 1541580530: Errors found in text ID 25723928576
owned by data record identified by RID = (1:171739:3) id
= 390945.
If I do a select statement for this ID, I get:
Server: Msg 601, Level 12, State 3, Line 1
Could not continue scan with NOLOCK due to data movement.
Does anyone have an ideas for why this happened? Other
actions I should try? Or the likelihood that this will
happen again?
Thanks greatly,
~BrendaHi Brenda,
From you description, it seems that you have resolved this problem and you
want to point out what induce this issue. If I have misunderstood, please
feel free to let me know.
Could you also send me the following logs/files so I can perform a more
analysis of what is happening:
1. SQLDiag.txt from the SQL Server 2000. To generate this file you need to
use the command line utility SQLDiag.exe -I instance_name . For more
information about the utility check SQL Books On-Line article "sqldiag
Utility"
2. System and application event logs saved as text files. To do that in
Windows NT
4.0 Event Viewer go to Log menu, choose Save As. From "Save As Type" dialog
box choose Text Files (*.TXT).
3. Any previous reports/output files of Maintenance Plan like the one you
have already attached.
4. Any DBCC CHECKDB results if you have run those manually, outside the
Maintenance Plan.
I am standing by for your response.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

Index Corruption

Hi,
What's the best approach to identify and fix index corruption problems. I
have some tables that corruption is occuring frequently.
Any idea ?
Vitor Mauricio
Frequent corruption is not normal. You would want to check
your hardware and the windows event logs for any indications
of hardware issues.
You can identify issues using dbcc checkdb. If you want to
limit the check to specific tables, you dbcc checktable.
The best approach to fixing the issues is to do root cause
analysis and determine what is causing the corruption and
then address those issues.
-Sue
On Mon, 18 Oct 2004 23:01:02 -0300, "Vitor Mauricio de N.
Silva" <vitor_mauricio@.newsgroup.nospam> wrote:

>Hi,
>What's the best approach to identify and fix index corruption problems. I
>have some tables that corruption is occuring frequently.
>Any idea ?
>Vitor Mauricio
>
|||Hi Vitor Mauricio,
I wanted to post a quick note to see if you would like additional
assistance or information regarding the information Sue had sent to you. We
appreciate your patience and look forward to hearing from you!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Corruption could happen if you are performing a few operations on a given table in batch without commiting them. Say for instance you delete a bunch of records, and perform an insert, then update a field. If you are doing this in a batch, then you could possibly run into a corruption issue due to the fact that the datbase hasnt committed the activity. The index "thinks" that there are more records there then there actually are, so in this case the index becomes corrupt and you must re-build it. One way around this is to separate your logic into more compartmentalized code or update statistics between operations.

Quote:

Originally posted by Vitor Mauricio de N. Silva
Hi,
What's the best approach to identify and fix index corruption problems. I
have some tables that corruption is occuring frequently.
Any idea ?
Vitor Mauricio

Index Corruption

Hi,
Does anyone know of an obvious reason for indexes to get corrupt or have the
statistics get really out of wack? We had a problem today where the
performance just went down the toilet.. It was really obvious that a
particular frequent query was taking 1000 times longer than normal.. The
query plan had a new start "Scan Constants?" I dropped and recreated
the index and the problem went away..
Why would it so suddenly go bad?
Thanks
Bill
Hi
Auto Create and Update Statistics On?
The SP could generate another query plan during the day it it finds it's
earlier estimations were wrong.
I have found that once the statistics get really out of date, queries going
souith are occuring more frequently.
There has been a bit of discussion on this in
microsoft.public.sqlserver.server and
microsoft.public.sqlserver.programming lately on it.
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/
"Bill" <someone@.somewhere.com> wrote in message
news:u9uGUCkNFHA.688@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Does anyone know of an obvious reason for indexes to get corrupt or have
> the statistics get really out of wack? We had a problem today where
> the performance just went down the toilet.. It was really obvious that
> a particular frequent query was taking 1000 times longer than normal..
> The query plan had a new start "Scan Constants?" I dropped and
> recreated the index and the problem went away..
> Why would it so suddenly go bad?
> Thanks
> Bill
>
|||Yep, Auto Create and Auto Update are on...
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:ujKN8FkNFHA.1396@.TK2MSFTNGP10.phx.gbl...
> Hi
> Auto Create and Update Statistics On?
> The SP could generate another query plan during the day it it finds it's
> earlier estimations were wrong.
> I have found that once the statistics get really out of date, queries
> going souith are occuring more frequently.
> There has been a bit of discussion on this in
> microsoft.public.sqlserver.server and
> microsoft.public.sqlserver.programming lately on it.
> 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/
> "Bill" <someone@.somewhere.com> wrote in message
> news:u9uGUCkNFHA.688@.TK2MSFTNGP10.phx.gbl...
>
|||That is not index corruption and by reindexing you simply forced a new plan
to be generated. You could have just recompiled the proc. This happens
occasionally. You can have a value that is atypical and requires a scan
where as all the others would use a seek. If the first time the proc was
compiled or recompiled it happened to get that bad value passed in the query
plan would be right for that one time but wrong for the majority of the
others.
Andrew J. Kelly SQL MVP
"Bill" <someone@.somewhere.com> wrote in message
news:u9uGUCkNFHA.688@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Does anyone know of an obvious reason for indexes to get corrupt or have
> the statistics get really out of wack? We had a problem today where
> the performance just went down the toilet.. It was really obvious that
> a particular frequent query was taking 1000 times longer than normal..
> The query plan had a new start "Scan Constants?" I dropped and
> recreated the index and the problem went away..
> Why would it so suddenly go bad?
> Thanks
> Bill
>

Index Corruption

Hi,
Does anyone know of an obvious reason for indexes to get corrupt or have the
statistics get really out of wack' We had a problem today where the
performance just went down the toilet.. It was really obvious that a
particular frequent query was taking 1000 times longer than normal.. The
query plan had a new start "Scan Constants'" I dropped and recreated
the index and the problem went away..
Why would it so suddenly go bad?
Thanks
BillHi
Auto Create and Update Statistics On?
The SP could generate another query plan during the day it it finds it's
earlier estimations were wrong.
I have found that once the statistics get really out of date, queries going
souith are occuring more frequently.
There has been a bit of discussion on this in
microsoft.public.sqlserver.server and
microsoft.public.sqlserver.programming lately on it.
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/
"Bill" <someone@.somewhere.com> wrote in message
news:u9uGUCkNFHA.688@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Does anyone know of an obvious reason for indexes to get corrupt or have
> the statistics get really out of wack' We had a problem today where
> the performance just went down the toilet.. It was really obvious that
> a particular frequent query was taking 1000 times longer than normal..
> The query plan had a new start "Scan Constants'" I dropped and
> recreated the index and the problem went away..
> Why would it so suddenly go bad?
> Thanks
> Bill
>|||Yep, Auto Create and Auto Update are on...
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:ujKN8FkNFHA.1396@.TK2MSFTNGP10.phx.gbl...
> Hi
> Auto Create and Update Statistics On?
> The SP could generate another query plan during the day it it finds it's
> earlier estimations were wrong.
> I have found that once the statistics get really out of date, queries
> going souith are occuring more frequently.
> There has been a bit of discussion on this in
> microsoft.public.sqlserver.server and
> microsoft.public.sqlserver.programming lately on it.
> 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/
> "Bill" <someone@.somewhere.com> wrote in message
> news:u9uGUCkNFHA.688@.TK2MSFTNGP10.phx.gbl...
>|||That is not index corruption and by reindexing you simply forced a new plan
to be generated. You could have just recompiled the proc. This happens
occasionally. You can have a value that is atypical and requires a scan
where as all the others would use a seek. If the first time the proc was
compiled or recompiled it happened to get that bad value passed in the query
plan would be right for that one time but wrong for the majority of the
others.
Andrew J. Kelly SQL MVP
"Bill" <someone@.somewhere.com> wrote in message
news:u9uGUCkNFHA.688@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Does anyone know of an obvious reason for indexes to get corrupt or have
> the statistics get really out of wack' We had a problem today where
> the performance just went down the toilet.. It was really obvious that
> a particular frequent query was taking 1000 times longer than normal..
> The query plan had a new start "Scan Constants'" I dropped and
> recreated the index and the problem went away..
> Why would it so suddenly go bad?
> Thanks
> Bill
>

Index Corruption

Hi,
What's the best approach to identify and fix index corruption problems. I
have some tables that corruption is occuring frequently.
Any idea ?
Vitor MauricioFrequent corruption is not normal. You would want to check
your hardware and the windows event logs for any indications
of hardware issues.
You can identify issues using dbcc checkdb. If you want to
limit the check to specific tables, you dbcc checktable.
The best approach to fixing the issues is to do root cause
analysis and determine what is causing the corruption and
then address those issues.
-Sue
On Mon, 18 Oct 2004 23:01:02 -0300, "Vitor Mauricio de N.
Silva" <vitor_mauricio@.newsgroup.nospam> wrote:

>Hi,
>What's the best approach to identify and fix index corruption problems. I
>have some tables that corruption is occuring frequently.
>Any idea ?
>Vitor Mauricio
>|||Hi Vitor Mauricio,
I wanted to post a quick note to see if you would like additional
assistance or information regarding the information Sue had sent to you. We
appreciate your patience and look forward to hearing from you!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Corruption could happen if you are performing a few operations on a
given table in batch without commiting them. Say for instance you
delete a bunch of records, and perform an insert, then update a field.
If you are doing this in a batch, then you could possibly run into a
corruption issue due to the fact that the datbase hasnt committed the
activity. The index "thinks" that there are more records there then
there actually are, so in this case the index becomes corrupt and you
must re-build it. One way around this is to separate your logic into
more compartmentalized code or update statistics between operations.
Vitor Mauricio de N. Silva wrote:
> *Hi,
> What's the best approach to identify and fix index corruption
> problems. I
> have some tables that corruption is occuring frequently.
> Any idea ?
> Vitor Mauricio *
mbroaddus
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message1165249.html