Data disappear - SQL server 2000 / ASP interface

[HB Kim]

Hello,
What could possibly cause data in the SQL server database to be
removed, except being deleted manually? We had a couple of situations
where data in certain records disappeared although the records were
still there. The data is entered and editted through the web interface
in ASP. The web interface is accessed by anyone who has an account in
our database.
I am more of a web programmer, not a SQL server administrator, so not
very familiar with SQL Server log or error tracking. If you can
suggest any way to track this kind of events (data disappearing), I
would appreciate it very much.

HB Kim

[Erland Sommarskog]

HB Kim (ha****@andrew.cmu.edu) writes:

What could possibly cause data in the SQL server database to be
removed, except being deleted manually? We had a couple of situations
where data in certain records disappeared although the records were
still there. The data is entered and editted through the web interface
in ASP. The web interface is accessed by anyone who has an account in
our database.
I am more of a web programmer, not a SQL server administrator, so not
very familiar with SQL Server log or error tracking. If you can
suggest any way to track this kind of events (data disappearing), I
would appreciate it very much.

I'm afraid that this description is a bit too vague to work from. Do I
understand you right that you had data for a car with the license
plate ABC123 like make, colour, placement of steering wheel etc. Now
you still have ABC123 in the database and can see it through the web
form, but there is no longer information about the make and colour?

For anyone who does not know your application, it is difficult to tell
whether this indicates an error or not. If you, as you say, users can
open and edit data, someone may have decided to erase some information -
or done so by mistake. If you want to protect yourself against this,
you may need to devise some permission scheme, so that not anyone can
change the data. You may also want to implement some auditing scheme.

There might of course be technical problems that causes the data go
away. Bugs happens in the best families.

The one recommendation I can give is to check out Lumgient Log Explorer
at http://www.lumigent.com/. This is a tool that permits you to
examine the transaction log, to find when and who submitted a certain
statement.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

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

[jean_bulinckx]

Hi,

I experienced data disappear. Four tables with a 1-n relationship are
heavily updated and are target of some big queries.

Cenario:
Table A with ~3.000 rows sometimes rollback updates
Table B with ~5.000 rows sometimes rollback updates and inserts
Table C with ~60.000 rows have several rollbacked inserts
Table D with ~2.500.000 rows is havely rollbacked

The workflow:
1) Users inserts a row at table A
2) Users inserts one or more rows at B
3) Users inserts lots of rows at C
4) Users inserts lots of rows at D
5) Users updates D
6) Users updates B and C
7) Users updates A and prints a report with all content from the four
tables concerning with the row from A
8) Minutes or hours later users checks for that report and updates A
9) Days later users print a big report from all month data

Problem:
At steps 8) and 9) users tell some rows from some tables are vanished!

Details:
All update, delete and insert are performed by Stored Procedures.
Some selects are performed by no indexed views.
Some selects use more than 12 joins!
Reports are printed before the data vanished.
Time to data disapear (at users discretion) varies from minutes to days.
Each insert, each update, each delete for EACH ROW, is a explicit
transaction.
I´m using MSSQLServer 2000 last sp and Windows Server 2000.
The server and users lies at the same network, the users uses a client
application (not web).
The user´s app connects with BDE but i coded a newer version with ADO but
this not worked.
All worked fine for sometime but sundenly this problem appears.
Users tell the loss of data is harder now the loss is growing.
The LAN posses 2 hub is slowdown from 100MB to 10MB all network (the
number of users is constantly growing).
All server memory is used, some locks take too long some deadlocks errors
arise.
I put a trigger to make sure any insert/update at the problematic tables
inputs a row at a dummy table (since i have the report i can search for
the row) no missing row are found at the dummy table.
Some triggers make a complex validations at some inserts/tables and
roolbacks the transaction, but when tested these triggers works fine.
I run checkdb, rebuild indexes checked realtionships, stored procedures,
application, triggers, etc.

Conclusions:
We need a better server with a lot more memory and a rebuild at the
network. ;)
This problem can be from a deadlock for sources like memory+network+diskIO
and the SQLServer cannot dont handle it.
The locks for sources are handled by windows but maybe the OS failed to
comunicate deadlocks problems to SQLserver.

Workaround:
I activated de server log to write any deadlock, timeout is -1(infinite) a
new server (and switches) are arriving shortly.
I hope this solves the problem. Someone experienced data loss like it?

Jean

[Simon Hayes]

"jean_bulinckx" <jc*@cin.ufpe.br> wrote in message
news:81******************************@localhost.ta lkaboutdatabases.com...

Hi,

I experienced data disappear. Four tables with a 1-n relationship are
heavily updated and are target of some big queries.

Cenario:
Table A with ~3.000 rows sometimes rollback updates
Table B with ~5.000 rows sometimes rollback updates and inserts
Table C with ~60.000 rows have several rollbacked inserts
Table D with ~2.500.000 rows is havely rollbacked

The workflow:
1) Users inserts a row at table A
2) Users inserts one or more rows at B
3) Users inserts lots of rows at C
4) Users inserts lots of rows at D
5) Users updates D
6) Users updates B and C
7) Users updates A and prints a report with all content from the four
tables concerning with the row from A
8) Minutes or hours later users checks for that report and updates A
9) Days later users print a big report from all month data

Problem:
At steps 8) and 9) users tell some rows from some tables are vanished!

Details:
All update, delete and insert are performed by Stored Procedures.
Some selects are performed by no indexed views.
Some selects use more than 12 joins!
Reports are printed before the data vanished.
Time to data disapear (at users discretion) varies from minutes to days.
Each insert, each update, each delete for EACH ROW, is a explicit
transaction.
I´m using MSSQLServer 2000 last sp and Windows Server 2000.
The server and users lies at the same network, the users uses a client
application (not web).
The user´s app connects with BDE but i coded a newer version with ADO but
this not worked.
All worked fine for sometime but sundenly this problem appears.
Users tell the loss of data is harder now the loss is growing.
The LAN posses 2 hub is slowdown from 100MB to 10MB all network (the
number of users is constantly growing).
All server memory is used, some locks take too long some deadlocks errors
arise.
I put a trigger to make sure any insert/update at the problematic tables
inputs a row at a dummy table (since i have the report i can search for
the row) no missing row are found at the dummy table.
Some triggers make a complex validations at some inserts/tables and
roolbacks the transaction, but when tested these triggers works fine.
I run checkdb, rebuild indexes checked realtionships, stored procedures,
application, triggers, etc.

Conclusions:
We need a better server with a lot more memory and a rebuild at the
network. ;)
This problem can be from a deadlock for sources like memory+network+diskIO
and the SQLServer cannot dont handle it.
The locks for sources are handled by windows but maybe the OS failed to
comunicate deadlocks problems to SQLserver.

Workaround:
I activated de server log to write any deadlock, timeout is -1(infinite) a
new server (and switches) are arriving shortly.
I hope this solves the problem. Someone experienced data loss like it?

Jean

So at step 7 the user can see data, but at step 8 it's not there? If so,
when does the transaction COMMIT? Before or after the user checks the data?
If the user views the data, and then the transaction rolls back, that might
explain it.

If not, you could run a Profiler trace filtered to show only DELETE
statements on the problem tables. That should tell you how and when the rows
are deleted.

Simon

[jean_bulinckx]

Hi Simon,

Each transaction (each insert, update) is immediately commited (well, at
least at the Stored procedures the code is ok "begin tran if @@error
rollback else commit"). The report at step 7) is printed after all job is
done.
Because a several lack of resources (memory and net) i´m not able to run a
profile at the server. When i try to open the managment folder at the
Enterprise Manager a "timeout" arise. The profiler is a useful tool but
make a overhead the server at this moment canot handle. I put triggers at
the tables just in case a bad user delete or change the rows but it dont
worked for the missing rows.
I´m inclined to a timeout or deadlock issue. I activated the server error
log to write about any deadlock. Now i searching for orphaned conections,
maybe a extend rollback is made for a connection broken or the transactin
is waiting for a not longer valid connection.
I´ll try to trace all rollbacks.
Remember not a single tran but lots os trans are missing here while
another bunch of trans works fine at the same time and for the same
session.

Jean

P.S.i´m using readcommited isolation

[Erland Sommarskog]

[posted and mailed, please reply in news]

jean_bulinckx (jc*@cin.ufpe.br) writes:

The workflow:
1) Users inserts a row at table A
2) Users inserts one or more rows at B
3) Users inserts lots of rows at C
4) Users inserts lots of rows at D
5) Users updates D
6) Users updates B and C
7) Users updates A and prints a report with all content from the four
tables concerning with the row from A
8) Minutes or hours later users checks for that report and updates A
9) Days later users print a big report from all month data

Problem:
At steps 8) and 9) users tell some rows from some tables are vanished!

First, if business rules requires all or none of all these rows to be
inserted, all should be packed into one transaction.

There are plenty of possible reasons, and without knowledge of your
application it is difficult to tell. The two main tracks are: a) someone
deleted the rows. b) the rows were never committed.

a) could be because of a badly coded program. I recall a horror story from
our application. We could find that random rows in one table were deleted,
and we had no idea of why. I eventually decided to track down the issue,
and found a function that stored procedure that for deleted rows from this
table. The problem was that id that was passed was declared as int in VB,
which is only 16 bits, so the function deleted the wrong rows. (And there
was not need to call the procedure, because the correct rows were handled
anyway.)

b) can happen if timeouts are not handled correctly. Timeouts are client-
side events of which SQL Server does not know about. When a client gets a
timeout, it cancels the query. But cancelling a query does not rollback
any outstanding transactions, even if the transaction was started by
the procedure that was cancelled. So after a timeout, you should always
issue a rollback. (Or disconnect and reconnect.) Or simply set the
timeout to 0, so you don't get them.

Since you say "some rows from some tables", I'm inclined to believe
that a) is more likely. But if you can deduced that all missing rows
are from the last part of the operation, improper timeout handling is
not an unlikely culprit.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

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

[jean_bulinckx]

Thx guys,

I canot tell too much about the application...

Just the DBA have total control over de database and server. But we are
redesining the server security.

Some more facts...

1) After a major tunning (focus at selects take less time) the number of
missing row decrease a lot!

2) The updates are not packed because i wish to validate rows at
onde-to-one basis and because i wish short transactions. A dozen short
transactions are prefearable than a big transaction.

3) The missing row affects the most frequent updated tables, the most used
and big tables are missind much more rows.

4) The row appear to be "deleted" at random.
if 100 rows are inserted or updated 10 are rollbacked.

I think its a timeout problem.

Jean

[jean_bulinckx]

Thx guys,

I canot tell too much about the application...

Just the DBA have total control over de database and server. But we are
redesining the server security.

Some more facts...

1) After a major tunning (focus at selects take less time) the number of
missing row decrease a lot!

2) The updates are not packed because i wish to validate rows at
onde-to-one basis and because i wish short transactions. A dozen short
transactions are prefearable than a big transaction.

3) The missing row affects the most frequent updated tables, the most used
and big tables are missind much more rows.

4) The row appear to be "deleted" at random.
if 100 rows are inserted or updated 10 are rollbacked.

I think its a timeout problem.

Jean

[Erland Sommarskog]

jean_bulinckx (jc*@cin.ufpe.br) writes:

I canot tell too much about the application...

Just the DBA have total control over de database and server. But we are
redesining the server security.
It is of course your choice how much information you want to share
about your application. But the less you tell us, the smaller is the
likelyhood that you will get useful advice.
2) The updates are not packed because i wish to validate rows at
onde-to-one basis and because i wish short transactions. A dozen short
transactions are prefearable than a big transaction.
To avoid contention, maybe. But when determining the transaction length,
the prime focus must be on business rules. If you do:

BEGIN TRANSACTION
INSERT A ...
COMMIT TRANSACTION

BEGIN TRANSACTION
INSERT B ...
COMMIT TRANSACTION

You must be sure that it is permissible that only the first transaction
is carried out. If your application would be inconsistent if this happened,
then you must have in all transaction. If you ignore that, then you should
be surprised if you are missing rows.

If you need to perform validation row-by-row, one option could be to use
temp tables to store the data, and not until you are done insert into
the target table. A variation is to use permanent tables that are
process-keyed. (A process-key can be as simple as @@spid, but that does
not work in a disconnected environment.)
4) The row appear to be "deleted" at random.
if 100 rows are inserted or updated 10 are rollbacked.

I think its a timeout problem.

Actually, to be honest, I think it is a problem with poor application
design.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

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

[jean_bulinckx]

Thx for the replys guys!

These inserts/updates are made by users, One-to-one. Sometimes a user
select a lot of rows to update or insert but at the cliente-side there a
loop to insert ono-to-one (i guess just to reuse the same insert
procedure).

I dont designed the app i´m just a fireman ;) working at server side.
This thing use ADO and i canot find a timeout handle at code. All i find
is a ordinary "try stored_procedure.execute catch"

I´m not sure ADO automatizes any sort of work about timeout but i thing
the app is not handling lock timeouts and deadlocks. The timeout is set to
0 what about close the connection while there are a blocked transaction?

Jean

[Erland Sommarskog]

jean_bulinckx (jc*@cin.ufpe.br) writes:

I´m not sure ADO automatizes any sort of work about timeout but i thing
the app is not handling lock timeouts and deadlocks. The timeout is set to
0 what about close the connection while there are a blocked transaction?

If the command timeout is set to 0, then there should not be any timeouts.
But it is important to set the timeout on the command object. The one on
the connection object is not inherited by the command object.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

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