A curious case of data corruption

[uli2003wien@lycos.at]

Dear group,

if someone could give me an idea what is going on in one of our
databases, this would really really be helpful.

We have two tables with around 2 / 3 million rows. These tables have no
key and no ID. (This major design flaw will be overcome in some later
version of the application-software working on this DB but right now i
have to live with this).

Now for the funny bit

1) I open one window in the Query-Analyzer and write some code like
Begin transaction INSERT INTO TABLE COMMIT
2) in another window i write "SELECT COUNT(*) from TABLE"

If I perform the insert then afterwards select count(*) the row-count
is incremented by two whereas the Insert-Statement said "1 row(s)
modified.

DBCC gives no errors.
DBCC gives amount of rows 2 million rows
Select count(*) on the same table gives 3 million rows

Exporting the data, truncating the table re-importing data gives no
result, right now the DTS-status is 203 and the machine is "thinking".

Is there any possibility to check the "integrity" of the table?

This problem is on the production machine, but right now i am working
on a copy so it was propagated with backup / restore-mechanism.

Any hint would be very helpful

Thanks and Greetings

Uli

[Erland Sommarskog]

Re: A curious case of data corruption

(uli2003wien@ly cos.at) writes:[color=blue]
> We have two tables with around 2 / 3 million rows. These tables have no
> key and no ID. (This major design flaw will be overcome in some later
> version of the application-software working on this DB but right now i
> have to live with this).
>
> Now for the funny bit
>
> 1) I open one window in the Query-Analyzer and write some code like
> Begin transaction INSERT INTO TABLE COMMIT
> 2) in another window i write "SELECT COUNT(*) from TABLE"
>
> If I perform the insert then afterwards select count(*) the row-count
> is incremented by two whereas the Insert-Statement said "1 row(s)
> modified.
>
> DBCC gives no errors.
> DBCC gives amount of rows 2 million rows
> Select count(*) on the same table gives 3 million rows[/color]

Well, I would definitely add a non-unique clustered index on the
table. It does not really matter which column, but if you add the
index, the entire table will be reorganized.

I recognize the symptom; other people have recommended similar observations.
Although they usually had a WHERE clause, and maybe even some indexes
on the table. I vaguely recall that a clustered index was a workaround
out of the problem.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Erland Sommarskog]

Re: A curious case of data corruption

(uli2003wien@ly cos.at) writes:[color=blue]
> We have two tables with around 2 / 3 million rows. These tables have no
> key and no ID. (This major design flaw will be overcome in some later
> version of the application-software working on this DB but right now i
> have to live with this).
>
> Now for the funny bit
>
> 1) I open one window in the Query-Analyzer and write some code like
> Begin transaction INSERT INTO TABLE COMMIT
> 2) in another window i write "SELECT COUNT(*) from TABLE"
>
> If I perform the insert then afterwards select count(*) the row-count
> is incremented by two whereas the Insert-Statement said "1 row(s)
> modified.
>
> DBCC gives no errors.
> DBCC gives amount of rows 2 million rows
> Select count(*) on the same table gives 3 million rows[/color]

Well, I would definitely add a non-unique clustered index on the
table. It does not really matter which column, but if you add the
index, the entire table will be reorganized.

I recognize the symptom; other people have recommended similar observations.
Although they usually had a WHERE clause, and maybe even some indexes
on the table. I vaguely recall that a clustered index was a workaround
out of the problem.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[uli2003wien@lycos.at]

Re: A curious case of data corruption



Erland Sommarskog schrieb:[color=blue]
> (uli2003wien@ly cos.at) writes:[color=green]
> > We have two tables with around 2 / 3 million rows. These tables have no
> > key and no ID. (This major design flaw will be overcome in some later
> > version of the application-software working on this DB but right now i
> > have to live with this).
> >
> > Now for the funny bit
> >
> > 1) I open one window in the Query-Analyzer and write some code like
> > Begin transaction INSERT INTO TABLE COMMIT
> > 2) in another window i write "SELECT COUNT(*) from TABLE"
> >
> > If I perform the insert then afterwards select count(*) the row-count
> > is incremented by two whereas the Insert-Statement said "1 row(s)
> > modified.
> >
> > DBCC gives no errors.
> > DBCC gives amount of rows 2 million rows
> > Select count(*) on the same table gives 3 million rows[/color]
>
> Well, I would definitely add a non-unique clustered index on the
> table. It does not really matter which column, but if you add the
> index, the entire table will be reorganized.
>
> I recognize the symptom; other people have recommended similar observations.
> Although they usually had a WHERE clause, and maybe even some indexes
> on the table. I vaguely recall that a clustered index was a workaround
> out of the problem.
>[/color]

Thank you Erland,

as always a great help and a hint for the right direction. Actually
this table had already a clustered index but dropping the index and
recreating the index did the job for me (and much faster than
exporting, dropping and importing the table)

Regards

Uli

[uli2003wien@lycos.at]

Re: A curious case of data corruption



Erland Sommarskog schrieb:[color=blue]
> (uli2003wien@ly cos.at) writes:[color=green]
> > We have two tables with around 2 / 3 million rows. These tables have no
> > key and no ID. (This major design flaw will be overcome in some later
> > version of the application-software working on this DB but right now i
> > have to live with this).
> >
> > Now for the funny bit
> >
> > 1) I open one window in the Query-Analyzer and write some code like
> > Begin transaction INSERT INTO TABLE COMMIT
> > 2) in another window i write "SELECT COUNT(*) from TABLE"
> >
> > If I perform the insert then afterwards select count(*) the row-count
> > is incremented by two whereas the Insert-Statement said "1 row(s)
> > modified.
> >
> > DBCC gives no errors.
> > DBCC gives amount of rows 2 million rows
> > Select count(*) on the same table gives 3 million rows[/color]
>
> Well, I would definitely add a non-unique clustered index on the
> table. It does not really matter which column, but if you add the
> index, the entire table will be reorganized.
>
> I recognize the symptom; other people have recommended similar observations.
> Although they usually had a WHERE clause, and maybe even some indexes
> on the table. I vaguely recall that a clustered index was a workaround
> out of the problem.
>[/color]

Thank you Erland,

as always a great help and a hint for the right direction. Actually
this table had already a clustered index but dropping the index and
recreating the index did the job for me (and much faster than
exporting, dropping and importing the table)

Regards

Uli

[Erland Sommarskog]

Re: A curious case of data corruption

(uli2003wien@ly cos.at) writes:[color=blue]
> as always a great help and a hint for the right direction. Actually
> this table had already a clustered index but dropping the index and
> recreating the index did the job for me (and much faster than
> exporting, dropping and importing the table)[/color]

That's good to hear. I would keep an eye on the table, in case the
problem would reappear.

By the way, rather than dropping and recreating, DBCC DBREINDEX can
be somewhat quicker. You can also use WITH DROP_EXISTING on CREATE INDEX.

This is particularly important if you have non-clustered indexes on the
table as well, as they will have to be rebuilt if you drop the clustered
index. This is because the NC indexes use the clustered index keys as
their row locator.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp