MS SQL Server - RE: SQL Server Throwing Rows

Author

RE: SQL Server Throwing Rows

Aaron Howard

2006-10-24, 6:28 pm

We ran into this problem as well with our SQL Server 2005 box. On one
table in one database about a week ago rows just started randomly
disappearing. There were no code changes, no triggers touching that
table, and there was no way for a user to delete a record from the main
page.

This has us stumped.

We checked database and disk integrity. No problems there. Then we
recopied the data lost over, but starting roughly at midnight, right
after we did a backup, the problem resurfaced. Finally I turned off
delete access for a particular user and that stopped the data loss.
However, this is a system user that everyone uses to connect to read the
data from a website. At no point does this user ID ever delete data.
At this point I noticed records disappearing on average of one a minute.

Next up, I extracted all the data from the table again, manually
recreating the table, and repopulating. Yet again the problem comes
back.

To track this better I created a trigger that logged the row id, user
name, computer name, and time when a record was lost. This kind of
worked, as it tracked showed the row ID, and time a row was lost but the
Computer Name and User Name were completely invalid... almost like it
didnt come from a user.

Finally, after looking through the database log I was able to determine
that roughly 50% of the time when one record was added to another table,
one was deleted from our problematic table. Happened time and time
again. No triggers on the table that was inserting the records. No
correlation one bit between the records either, not like they were
shared a foreign key or anything...

It was almost like if someones head was too small and every time they
had a new piece of information in, something else came out the other
side.

While we stopped the bleeding, and have a vague idea of the cause, if
anyone else has any better ideas on what is the issue, please let me
know.

Thanks,

Aaron Howard
(8 years Software Developer, SQL Server Admin)

*** Sent via Developersdex http://www.droptable.com ***

JXStern

2006-10-24, 6:28 pm

On Fri, 15 Sep 2006 10:26:58 -0700, Aaron Howard
<alhoward@hotmail.com> wrote:
>While we stopped the bleeding, and have a vague idea of the cause, if
>anyone else has any better ideas on what is the issue, please let me
>know.

Yikes.

SO, want to share the vague idea?

Especially if there's anything in the DDL involved?

J.

Sponsored Links