How was the record deleted!!

Question

Post reply

How was the record deleted!!

SJanki

SSCrazy

Points: 2847
More actions
August 14, 2012 at 3:01 pm

#262879

Hi All
I am having a strange issue.
From .NET application 5 records are inserted in a Table A.
These records are inserted at datetime=2012-08-14 14:14:49.000
There is another functionality where another user , from the .net application, approves these records.
The code here is not touching table A at all.
It is only inserting a record in another table TableAudit.
In TableAudit the record is inserted at 2012-08-14 14:28:20.000
When the user displays the records what he inserted in Table A, 1 record is missing.
From SQL server, I check the Table A and it seems as if that record was never inserted. Its not there.
But according to the validations in the application this record must have been inserted ( i am sure because in the table the ids exist from 78869 to 78874 but 78871 is missing) , so I go and see the delete log.
It shows a record was deleted from this table at the same time when the record was being inserted in TableAudit
Such logic is no-where in the application to delete 1 record from Table A while inserting record in TableAudit.
Any idea how this can happen in SQL Server? How can I find more information on this deleted record.
Thanks!

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

rVadim Hall of Fame Points: 3969 More actions · Answer 1

rVadim

Hall of Fame

Points: 3969

August 14, 2012 at 3:08 pm

#1525805

Are there any triggers on those tables?

--Vadim R.

SJanki SSCrazy Points: 2847 More actions · Answer 2

rVadim (8/14/2012)
Are there any triggers on those tables?

No triggers.

Lowell SSC Guru Points: 323518 More actions · Answer 3

if a transaction rolls back or fails, the identity() columns still increment.

it sounds like an error occurred inserting the data that was not handled/reported by the .net application.

i've seen things form an app, like sticking zero in a foreign key column, or leaving values null for NOT NULL columns, for example.

those things return an error from SQL, but the app might jsut be assuming that the data was updated/inserted, without displaying any errors returned.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

rVadim Hall of Fame Points: 3969 More actions · Answer 4

78869 to 78874 - that is 6 records you said you inserted 5 ?

--Vadim R.

SJanki SSCrazy Points: 2847 More actions · Answer 5

rVadim (8/14/2012)
78869 to 78874 - that is 6 records you said you inserted 5 ?

I also add that 78871 was missing.

78869

78870

78872

78873

78874

Sorry for the confusion

SJanki SSCrazy Points: 2847 More actions · Answer 6

Lowell (8/14/2012)
if a transaction rolls back or fails, the identity() columns still increment.
it sounds like an error occurred inserting the data that was not handled/reported by the .net application.
i've seen things form an app, like sticking zero in a foreign key column, or leaving values null for NOT NULL columns, for example.
those things return an error from SQL, but the app might jsut be assuming that the data was updated/inserted, without displaying any errors returned.

I used this to see if any deletions happened on that table , and it gave me 1 [Transaction ID]

So its like the record was inserted and then deleted?? isn't it?

DECLARE @TableName sysname

SET @TableName = 'dbo.Table A'

SELECT

[Transaction ID]

FROM

fn_dblog(NULL, NULL)

WHERE

AllocUnitName LIKE @TableName + '%'

AND

Operation = 'LOP_DELETE_ROWS'

Ness SSCarpal Tunnel Points: 4282 More actions · Answer 7

You could always run a server side trace looking for the events and especially the TM: Rollback Tran Starting/Completed event to spot if the transaction was rolled back

SQL DBA
Every day is a school day, and don't trust anyone who tells you any different.
http://sqlblogness.blogspot.co.uk

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

SJanki (8/14/2012)
Hi All
I am having a strange issue.
From .NET application 5 records are inserted in a Table A.
These records are inserted at datetime=2012-08-14 14:14:49.000
There is another functionality where another user , from the .net application, approves these records.
The code here is not touching table A at all.
It is only inserting a record in another table TableAudit.
In TableAudit the record is inserted at 2012-08-14 14:28:20.000
When the user displays the records what he inserted in Table A, 1 record is missing.
From SQL server, I check the Table A and it seems as if that record was never inserted. Its not there.
But according to the validations in the application this record must have been inserted ( i am sure because in the table the ids exist from 78869 to 78874 but 78871 is missing) , so I go and see the delete log.
It shows a record was deleted from this table at the same time when the record was being inserted in TableAudit
Such logic is no-where in the application to delete 1 record from Table A while inserting record in TableAudit.
Any idea how this can happen in SQL Server? How can I find more information on this deleted record.
Thanks!

You've stated that 5 rows are inserted from the app - and 5 rows remain.

If you're inserting 5 rows one at a time, what prevents another process from inserting then deleting one row in the middle of those inserts? Where did the sixth row come from?

Why aren't you inserting the 5 rows in one statement?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden