Is this even Possible?

Hi,
I have a very strange problem and just wanted to run something with
you guys to see if this scanario is possible.
Is it possible that data inserted into a table using an insert query
within a stored procedure and called from an ASP page could be
selected by a diferent ASP page calling a stored procedure. And for
that whole data to suddenly disappear without a trace?
We have a site which has been running fine for a year, and recently it
has been hit quite badly in terms of traffic. When a user comes to
the site, data is eneterd into a user table and the Identity
requested. Then extra info is taken from that user and entered into a
second table using the identity (two different stored procdures. Then
an audit (this identity, and one other piece of info) is written to
the hard drive using a flat file, this happens for reasons I dont
really want to go into, but nonetheless this happens.
We have had a number of users coming back to us stating that they have
used the site and quoted a reference (the identity) this is completely
missing, including the data associated with it in the second table
(The flat file is there however, which means a select must have taken
place and data must have been there at some point). In one case,
there was only 10 minutes between the user entering data and us
finding it didnt exist. The identity in the flat file DOES exist.
Where could this data go? The SP uses a commit at the end and this is
happenening (After all, we would be able to select from this data
unless it was commited) The sequential number is missing from the
identity field in the first table.
We have performed a backup and rollforward using the transaction logs
and NONE of these SQL transactuions appear to have taken place, and do
not appear in the transaction log at all.
We have been looking into lots of things regarding torn pages but our
understanding is that if this had happened, the data should still be
in the transaction log for that day?
the server was under considerable strain with a queue index of over 10
so could this affect it? We have used SQL server (7 and 2000) for a
number of years now and have never experienced anything like this.
Logic would state that the data was in there but has subsequently been
deleted, but the data doesnt exist in the transaction logs at all so
how did it disappear (theres no INSERT or DELETE)?
Any thoughts would be very much appreciated.
Craig
---
Play my Pot Noodle Game
http://www.dvdhacks.co.uk/games.aspA quick idea: maybe some transactions get rolled back because of a deadlock?
Try to catch error 1205.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Craig" <reply2craig@.hotmail.com> wrote in message
news:flk40115pt56mnvrtqf0ol3076186pefm6@.4ax.com...
> Hi,
> I have a very strange problem and just wanted to run something with
> you guys to see if this scanario is possible.
> Is it possible that data inserted into a table using an insert query
> within a stored procedure and called from an ASP page could be
> selected by a diferent ASP page calling a stored procedure. And for
> that whole data to suddenly disappear without a trace?
> We have a site which has been running fine for a year, and recently it
> has been hit quite badly in terms of traffic. When a user comes to
> the site, data is eneterd into a user table and the Identity
> requested. Then extra info is taken from that user and entered into a
> second table using the identity (two different stored procdures. Then
> an audit (this identity, and one other piece of info) is written to
> the hard drive using a flat file, this happens for reasons I dont
> really want to go into, but nonetheless this happens.
> We have had a number of users coming back to us stating that they have
> used the site and quoted a reference (the identity) this is completely
> missing, including the data associated with it in the second table
> (The flat file is there however, which means a select must have taken>
place and data must have been there at some point). In one case,
> there was only 10 minutes between the user entering data and us
> finding it didnt exist. The identity in the flat file DOES exist.
> Where could this data go? The SP uses a commit at the end and this is
> happenening (After all, we would be able to select from this data
> unless it was commited) The sequential number is missing from the
> identity field in the first table.
> We have performed a backup and rollforward using the transaction logs
> and NONE of these SQL transactuions appear to have taken place, and do
> not appear in the transaction log at all.
> We have been looking into lots of things regarding torn pages but our
> understanding is that if this had happened, the data should still be
> in the transaction log for that day?
> the server was under considerable strain with a queue index of over 10
> so could this affect it? We have used SQL server (7 and 2000) for a
> number of years now and have never experienced anything like this.
> Logic would state that the data was in there but has subsequently been
> deleted, but the data doesnt exist in the transaction logs at all so
> how did it disappear (theres no INSERT or DELETE)?
> Any thoughts would be very much appreciated.
> Craig
>
> ---
> Play my Pot Noodle Game
> http://www.dvdhacks.co.uk/games.asp|||On Thu, 3 Feb 2005 20:07:12 +0100, "Dejan Sarka"
<dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote:
>A quick idea: maybe some transactions get rolled back because of a deadlock?
>Try to catch error 1205.
Okay, thanks for that...... looking back though, would error 1205
show up in any logs?
Cheers
Craig
---
Play my Pot Noodle Game
http://www.dvdhacks.co.uk/games.asp|||Craig wrote:
> Hi,
> I have a very strange problem and just wanted to run something with
> you guys to see if this scanario is possible.
> Is it possible that data inserted into a table using an insert query
> within a stored procedure and called from an ASP page could be
> selected by a diferent ASP page calling a stored procedure. And for
> that whole data to suddenly disappear without a trace?
> We have a site which has been running fine for a year, and recently it
> has been hit quite badly in terms of traffic. When a user comes to
> the site, data is eneterd into a user table and the Identity
> requested. Then extra info is taken from that user and entered into a
> second table using the identity (two different stored procdures. Then
> an audit (this identity, and one other piece of info) is written to
> the hard drive using a flat file, this happens for reasons I dont
> really want to go into, but nonetheless this happens.
> We have had a number of users coming back to us stating that they have
> used the site and quoted a reference (the identity) this is completely
> missing, including the data associated with it in the second table
> (The flat file is there however, which means a select must have taken
> place and data must have been there at some point). In one case,
> there was only 10 minutes between the user entering data and us
> finding it didnt exist. The identity in the flat file DOES exist.
> Where could this data go? The SP uses a commit at the end and this is
> happenening (After all, we would be able to select from this data
> unless it was commited) The sequential number is missing from the
> identity field in the first table.
> We have performed a backup and rollforward using the transaction logs
> and NONE of these SQL transactuions appear to have taken place, and do
> not appear in the transaction log at all.
> We have been looking into lots of things regarding torn pages but our
> understanding is that if this had happened, the data should still be
> in the transaction log for that day?
> the server was under considerable strain with a queue index of over 10
> so could this affect it? We have used SQL server (7 and 2000) for a
> number of years now and have never experienced anything like this.
> Logic would state that the data was in there but has subsequently been
> deleted, but the data doesnt exist in the transaction logs at all so
> how did it disappear (theres no INSERT or DELETE)?
> Any thoughts would be very much appreciated.
> Craig
>
> ---
> Play my Pot Noodle Game
> http://www.dvdhacks.co.uk/games.asp
Just a thought,
You are doing the inserts and stuff with SPs, there is a possibility the
SPs arent functioning properly, if in any of the porgram steps inside
the SP gets and warning message it just dies, NO error is returned as
the @.@.ERROR does not contain warning messages but the SP will exit
and rollback will occur.
this just might be the cause .