数据消失 - SQL server 2000 / ASP接口 [英] Data disappear - SQL server 2000 / ASP interface

问题描述

你好，

除了被手动删除外，什么可能导致SQL服务器数据库中的数据被删除？b $ b被删除？我们有几种情况

，其中某些记录中的数据消失了，尽管记录仍然是那里的b $ b。数据通过Web界面输入和编辑

在ASP中。任何在我们的数据库中拥有帐户的人都可以访问网络界面。

我更像是一个网络程序员，而不是SQL服务器管理员，所以不是

非常熟悉SQL Server日志或错误跟踪。如果你能以
建议任何方式来跟踪这类事件（数据消失），我将非常感激。


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

推荐答案

HB Kim（ha****@andrew.cmu.edu）写道：

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

什么可能导致SQL服务器数据库中的数据被删除，除了手动删除？我们有几种情况
，虽然记录仍然存在，但某些记录中的数据消失了。数据通过ASP中的Web界面输入和编辑。任何在我们的数据库中拥有帐户的人都可以访问Web界面。
我更像是一个Web程序员，而不是SQL服务器管理员，所以不是非常熟悉SQL Server日志或者错误跟踪。如果你能建议任何方式来跟踪这类事件（数据消失），我将非常感激。

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.

我'恐怕这种描述有点太模糊了。我是否b $ b了解你是否拥有使用许可证的汽车数据

板ABC123如制造，颜色，方向盘放置等。现在

您在数据库中仍然有ABC123，可以通过网页

表格查看，但不再有关于品牌和颜色的信息吗？


对于不了解您的申请的任何人，很难说这是否表明错误。如果你，如你所说，用户可以打开和编辑数据，有人可能已经决定删除​​一些信息 -

或错误地完成。如果你想保护自己免受这种情况的影响，你可能需要设计一些权限方案，这样就不会有任何人能够改变数据了。您可能还想实施一些审计方案。


当然可能存在技术问题导致数据消失了。错误发生在最好的家庭中。


我可以给出的一个建议是查看Lumgient Log Explorer

http://www.lumigent.com/ 。这是一个工具，允许您检查交易日志，查找何时以及谁提交了某个

语句。


-

Erland Sommarskog，SQL Server MVP， so****@algonet.se


SQL Server SP3联机丛书
http://www.microsoft.com/sql/techinf...2000/books.asp

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

我经历的数据消失了。四张1-n关系的桌子

大幅更新，是一些大问题的目标。


Cenario：

表A~3000行有时回滚更新

表B有~5000行有时回滚更新和插入

表C有~60.000行有几个回滚插入

表D有~2.500.000行已经回滚


工作流程：

1）用户在表A插入一行

2）用户在B处插入一行或多行

3）用户在C处插入大量行

5）用户更新D

6）用户更新B和C

7）用户更新A并打印包含四个内容的报告

关于来自A的行的表
8）几分钟或几小时后，用户检查该报告并更新A

9）几天后用户打印所有月份数据的重大报告


问题：

在步骤8）和9）用户告诉某些表中的某些行消失了！


详细信息：

所有更新，删除和插入都由存储过程执行。

有些选择是由没有索引的视图执行的。

有些选择使用超过12个连接！

报告在数据消失之前打印。

数据消失的时间（由用户自行决定）从几分钟到几天不等。

每次更新，每次更新，每次删除EACH ROW，都是明确的

交易。

我正在使用MSSQLServer 2000 last sp和Windows Server 2000.

服务器和用户位于同一网络，用户使用客户端

应用程序（不是网络）。

用户的应用程序与BDE连接，但我用ADO编写了一个较新的版本，但

这个没用。

一切都工作正常但很明显出现这个问题。

用户告诉数据丢失现在损失越来越大。

局域网pos ses 2 hub从100MB减速到10MB所有网络（

用户数量不断增长）。

使用所有服务器内存，一些锁需要太长时间一些死锁错误

出现。

我设置一个触发器以确保在有问题的表格上插入/更新

在虚拟表格中输入一行（因为我有报告我可以搜索

行）在虚拟表中找不到丢失的行。

某些触发器在某些插入/表格中进行复杂的验证

roolbacks交易，但是经过测试，这些触发器工作正常。

我运行checkdb，重建索引检查的实际情况，存储过程，

应用程序，触发器等。


结论：

我们需要更好的服务器，内存更多，并且需要在

网络上重建。 ;）

这个问题可能来自于内存+网络+磁盘等来源的死锁，而b + b不能处理它。

源代码的锁是由windows处理的，但操作系统可能无法向SQL服务器发送死锁问题。


解决方法：

I激活de服务器日志写入任何死锁，超时为-1（无限）a

新服务器（和交换机）很快到达。

我希望这可以解决问题。有人经历过像这样的数据丢失吗？


Jean

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

" jean_bulinckx" ; < jc*@cin.ufpe.br>在消息中写道

news：81 ****************************** @ localhost.ta lkaboutdatabases。 com ...

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

我经历的数据消失了。四个具有1-n关系的表格被大量更新，并且是一些大查询的目标。

Cenario：
表A有~3000行有时回滚更新
表B有~5000行有时回滚更新和插入表C有~60.000行有几个回滚插页
表D有~2.5万行已经回滚

工作流程：1）用户在表A处插入一行
2）用户在B处插入一行或多行
3）用户在C处插入大量行
4）用户插入批次用户更新D
5）用户更新B和C
7）用户更新A并打印一份报告，其中包含来自四个表格的所有内容。来自A
的行8）几分钟或几小时后，用户检查该报告并更新A
9）几天后，用户打印所有月份数据的大报告

问题：<在步骤8）和9）用户告诉某些表中的某些行消失了！

详细信息：
所有更新，删除和插入都由存储过程执行。
有些选择是由没有索引视图执行的。
有些选择使用超过12个连接！
报告在数据消失之前打印。数据消失的时间（由用户自行决定）从几分钟到几天不等。
每次插入，每次更新，每次删除EACH ROW，都是明确的交易。
我是使用MSSQLServer 2000 last sp和Windows Server 2000.
服务器和用户位于同一网络，用户使用客户端应用程序（不是网络）。
用户的应用程序连接BDE但是我使用ADO编写了一个更新的版本但是
这没有用。
一切都工作正常，但是很明显出现了这个问题。
用户告诉数据丢失现在更难以增加。局域网拥有2个集线器从100MB减速到10MB所有网络（用户数量不断增长）。
所有服务器内存都被使用，有些锁需要太长时间一些死锁rors
出现。
我设置了一个触发器，以确保在有问题的表格上的任何插入/更新
在虚拟表格中输入一行（因为我有报告，我可以搜索
行）在虚拟表中没有找到丢失的行。
某些触发器在某些插入/表格中进行复杂的验证并且对事务进行了反击，但是在测试时这些触发器工作正常。
我运行checkdb，重建索引检查的实现，存储过程，应用程序，触发器等。

结论：
我们需要一个更好的服务器，有更多的内存和重建
网络。 ;）
这个问题可能来自内存+网络+磁盘等来源的死锁，而SQLServer也无法处理它。
源的锁由Windows处理但可能操作系统失败向SQLserver通报死锁问题。

解决方法：
我激活了de server log来写任何死锁，超时是-1（无限）一个新服务器（很快就会到达。
我希望这能解决问题。有人经历过这样的数据丢失吗？

Jean

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

所以在第7步，用户可以看到数据，但是在第8步，它是'不在那里？如果是这样，

什么时候交易COMMIT？在用户检查数据之前或之后？

如果用户查看数据，然后事务回滚，那可能会解释它。


如果没有，您可以运行一个过滤的Profiler跟踪，以便在问题表上仅显示DELETE

语句。这应该告诉你如何以及何时删除行

。


Simon

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

这篇关于数据消失 - SQL server 2000 / ASP接口的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！