missing rows problem

Hi!
Recently I have noticed some strange behaviour from SQL Server.
It seems to me that rows are being deleted some time after they are
inserted (or maybe they are unable to be read again).
I use one Trial SQL server 2000 as central server with Merge replication
and MSDE as anonymous subscription.
When I used only Trial server with no replication I didn't notice this
problem (I mean, I didn't get reports of strange behaviour of data
from users using the program).
Couple a days ago I got first report of strange behaviour of data
not being on the server. I said that he is probably mistaken and
that he didn't do something right. So I went to check log of the
data where I found out that he was right, and that SQL server
deleted three rows.
I know this because there is a log with primary key of the data
that were inserted. This log is created inside transaction after
the data is inserted. And there is no stored procedure for deleting
this data.
So he inserted that same rows again, and again for some reasons they
were also deleted after some time. Now I had two logs of the same data
being inserted and not present.
After the third time the data remained there.
Today I got a report of another problem. One row is also missing, but
this time from another table.
I have a couple of data which proves that this row was inserted and
unless using sql commands it can't be removed from database.
Users can only make changes to the data on the Trial server and then
the data are propagated to MSDE server.
I found http://support.microsoft.com/default.aspx?scid=kb;en-us;826433
which could be related but I'm not sure what to do.
Any suggestions on how to approach this problem? How to detect when and
why are data being removed?From which server are they missing the publisher, subscriber or both?
--
Nik Marshall-Blank MCSD/MCDBA
"zapov" <zapov@.yahoo.com> wrote in message
news:dfrfef$1b5$1@.ss405.t-com.hr...
> Hi!
> Recently I have noticed some strange behaviour from SQL Server.
> It seems to me that rows are being deleted some time after they are
> inserted (or maybe they are unable to be read again).
> I use one Trial SQL server 2000 as central server with Merge replication
> and MSDE as anonymous subscription.
> When I used only Trial server with no replication I didn't notice this
> problem (I mean, I didn't get reports of strange behaviour of data
> from users using the program).
> Couple a days ago I got first report of strange behaviour of data
> not being on the server. I said that he is probably mistaken and
> that he didn't do something right. So I went to check log of the
> data where I found out that he was right, and that SQL server
> deleted three rows.
> I know this because there is a log with primary key of the data
> that were inserted. This log is created inside transaction after
> the data is inserted. And there is no stored procedure for deleting
> this data.
> So he inserted that same rows again, and again for some reasons they
> were also deleted after some time. Now I had two logs of the same data
> being inserted and not present.
> After the third time the data remained there.
> Today I got a report of another problem. One row is also missing, but
> this time from another table.
> I have a couple of data which proves that this row was inserted and unless
> using sql commands it can't be removed from database.
>
> Users can only make changes to the data on the Trial server and then
> the data are propagated to MSDE server.
> I found http://support.microsoft.com/default.aspx?scid=kb;en-us;826433
> which could be related but I'm not sure what to do.
> Any suggestions on how to approach this problem? How to detect when and
> why are data being removed?|||Nik Marshall-Blank wrote:
> From which server are they missing the publisher, subscriber or both?
>
both|||After thinking about this I have one more question:
Could it be that because of referential integrity at the MSDE
server the data could be pushed back as a delete to Trial SQL
server?
Right now I have Enforce relationship for replication checked.
Should I unckeck it?|||You could try it.
--
Nik Marshall-Blank MCSD/MCDBA
"zapov" <zapov@.yahoo.com> wrote in message
news:dfrhnd$7qu$1@.ss405.t-com.hr...
> After thinking about this I have one more question:
> Could it be that because of referential integrity at the MSDE
> server the data could be pushed back as a delete to Trial SQL
> server?
> Right now I have Enforce relationship for replication checked.
> Should I unckeck it?|||Just read up on Merge Replications and updates at Subscribers are propogated
back to Publishers si I think this it=s your problem. Maybe you should go
for a different type of replication where subscriber changes are not
progpogated back.
--
Nik Marshall-Blank MCSD/MCDBA
"zapov" <zapov@.yahoo.com> wrote in message
news:dfrhnd$7qu$1@.ss405.t-com.hr...
> After thinking about this I have one more question:
> Could it be that because of referential integrity at the MSDE
> server the data could be pushed back as a delete to Trial SQL
> server?
> Right now I have Enforce relationship for replication checked.
> Should I unckeck it?

Missing records after COMMIT TRAN

Hi

Can you think about any reason for why when using a transaction after the COMMIT TRAN the inserted new record is not in the table and there is a gap in the identity??

I'm using SQL 2000 SP3, there are no triggers are on the table and it happanes only under heavy load.

Thanks,
Inon.http://www.dbforums.com/showthread.php?t=1196943|||The process or the environment are irrelevant, although the question is general the problem is very specific, people may have encountered the same symptom with a totally different situation.
I want to hear all the reasons that you can think of to when a transaction is committed and the record that was inserted in the transaction is missing.
One example is when a trigger is responsible to delete the record or prevent it from being insert like an INSTEAD OF trigger, but there are no triggers on the table, so what else can cause it??

Thanks,
Inon.|||a bug in your code.|||a bug in your code.

ok... thanks!! what else?

(First of all you can see that I wrote "UNDER HEAVY LOAD", meaning it's working fine most of the time and only under heavy load (of the same process) SOME of the records are missing, second, A bug would cause a rollback, and I will also add that @.@.IDENTITY returns the correct new identity of the record which is SOMETIMES missing and cause the gap in the table)

Inon.|||have you ran a trace yet and stepped through it yet?|||have you ran a trace yet and stepped through it yet?

No, the thing is, it's not my code, I'm trying to help someone, he already tried some debug prints and debug queries (inserting some information in the middle of the code), it seems that it's going the way it should, remember that it's working most of the time, what makes me believe it's not the code, but I could be wrong, I know that when it's a question of the computer or the human, the human is usually the blame.

I don't see how can a trace assist? Please advise.

Addition, the server is rather slow and with only 512 MB of RAM, the HD has about 3 GB of free space, but anyway I would expect to get some error or some message… anything, but no, all is going well yet records are missing and a gap is formed.

Thanks,
Inon.|||a properly setup profiler trace can you show you many things. sometimes things do not quite fire off like you expect them to. For this I would probably use the default columns and and use only the T-SQL and Stored procedure event classes and and all of the events that end with :completed.

It takes a little reading and some practice to interpert what you are looking at but Profiler helps me diagnose stuff everyday.|||You’re right, I do use trace sometimes, but I hoped that maybe someone who has already experienced this problem could pinpoint the cause and save me some time.

Addition, BTW I have a solution for this, I just do things a bit differently, but its still curios me, strange...

Thanks,
Inon.|||OK ... here's a few.

1. The row was deleted by a subsequent transaction

2. Even though the commit was issued, the transaction was rolled back (more specifically where a two-phase commit is required.)

3. The commit was issued, but the transaction timed out before completion (web based).

4. It fell off into the bit bucket. ;)