Suddenly Data Loss

  • Hi all

    Does anyone know what would cause data loss on tables after commit with no errors raised neither executed DELETEs?

    Is this a symptom of database corruption?

    I have seen this just twice from thousands records, using exactly the same Net Frmework code and Stored Procedure, suddenly there's no record in two tables that always must have data.

    There's no chance to avoid inserting in those tables.

    thanks for any advice.

    Nemachtiani

  • There has to be either a rollback occurring or another process performing a delete.

    If this is something that happens on a regular basis, you need to build a trace to monitor that table or implement auditing on the table so you can track when the rows are deleted.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Thanks a lot Jeffrey

    I'm looking at the .Net code and for each user process request there's one SqlClient.SqlTransaction running 5 SPs, each inserting in a respective table.

    There's a try ... catch block and rollbacks the SqlTransaction whatever exception occurrs and it is published on Event Logs, etc.

    For this particular case I only see data on one table, there's no data in other 4, and there's no exception detected on log files.

    Funny thing is there's another set of process requests completed (close to the one incomplete) generated as usually by the same user.

    Nemachtiani

  • If you have a transaction surrounding 5 stored procedure calls, and each call should result in a single row in each table that you can track to - then you should not see only a single row in just one table.

    This tells me that something is happening to commit the transaction for the first SP and the rest of the code is getting rolled back.

    Do you have explicit BEGIN TRANSACTION and COMMITs in each stored procedure?

    Is it possible that somewhere after the first stored procedure runs, you get an explicit commit - and later in the process you are getting an implicit rollback because the connection is terminated? Or, is it possible that an error is occurring that isn't trapped?

    Like I said before, start a profiler trace and capture the actual calls to the database.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Nemachtiani (6/22/2011)


    Hi all

    Does anyone know what would cause data loss on tables after commit with no errors raised neither executed DELETEs?

    Is this a symptom of database corruption?

    No.

    I have seen this just twice from thousands records, using exactly the same Net Frmework code and Stored Procedure, suddenly there's no record in two tables that always must have data.

    Someone's run a delete or truncate on the tables. Could be manually, could be from app. You'll have to run profiler to tell.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thanks a lot for your suggestions

    I try to replicate it closing connection but there's always an exception that's catched

    It happens again recently, with same symptoms, in a different source, but this time there was an exception:

    "System.InvalidOperationException:

    ExecuteNonQuery: Connection property has not been initialized.

    en System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean async)

    en System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)

    en System.Data.SqlClient.SqlCommand.ExecuteNonQuery() "

    this exception was catched, in the catch block the method executes the tran.Rollback, but it still persisted data of the SP that uses begin tran ... commit tran

    the exception happens when it tries to run this

    Dim cmd2 As New SqlCommand("SET ARITHABORT ON", cn, tran)

    cmd2.ExecuteNonQuery()

    "cn" was initilialized from tran.Connection, at this point 5 SPs has been executed with the same tran instance, and before executing a 6th SP (that affects another tables on same DB) the "SET ARITHABORT ON" is executed

    Now I would like to know what makes this Connection just dissappear and at same time why the tran.Rollback() doesn´t throw another exception

    Dim cn As New SqlClient.SqlConnection("[valid connectionstring]")

    cn.Open()

    Try

    ... -- Lots of code, where in some function InvalidOperationException raises

    tran.Commit()

    catch as System.Exception

    tran.Rollback()

    throw

    Finally

    If not cn is nothing and also cn.state= ConnectionState.Open then

    cn.Close()

    End If

    End Try

    Any ideas?

    Nemachtiani

  • Nemachtiani (6/22/2011)


    Hi all

    Does anyone know what would cause data loss on tables after commit with no errors raised neither executed DELETEs?

    Is this a symptom of database corruption?

    I have seen this just twice from thousands records, using exactly the same Net Frmework code and Stored Procedure, suddenly there's no record in two tables that always must have data.

    There's no chance to avoid inserting in those tables.

    thanks for any advice.

    do you have any triggers? a trigger rolling back because it was poorly coded could cause the original command(insert, update, etc) to rollback as well, resulting an an apparrent data loss.

    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!

  • Hi Lowell

    I've thinking about it but in that case it should happen every time we process the same input...

    We reprocess the input and it commits ok, without ANY source code or SP change...

    the first time i post this the input comes from a web page and I wasn't able to find an exception

    this time comes from a kind of batch input so we were able to reprocess and also we get an exception but the symptoms are the same: only data from a SP from 6 was persisted, the one that internally uses BEGIN TRAN... COMMIT TRAN

    thanks for your comment!

    Nemachtiani

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

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