June 22, 2011 at 4:16 pm
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
June 22, 2011 at 5:36 pm
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
June 22, 2011 at 6:27 pm
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
June 22, 2011 at 6:37 pm
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
June 23, 2011 at 12:50 am
Nemachtiani (6/22/2011)
Hi allDoes 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
July 25, 2011 at 12:54 pm
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
July 25, 2011 at 12:58 pm
Nemachtiani (6/22/2011)
Hi allDoes 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
July 25, 2011 at 1:11 pm
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