How was the record deleted!!

  • 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!

  • Are there any triggers on those tables?

    --Vadim R.

  • rVadim (8/14/2012)


    Are there any triggers on those tables?

    No triggers.

  • 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!

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

    --Vadim R.

  • 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

  • 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'

  • 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

  • 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

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

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