Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Suddenly Data Loss Expand / Collapse
Author
Message
Posted Wednesday, June 22, 2011 4:16 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, February 18, 2014 11:39 AM
Points: 11, Visits: 68
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
Post #1130048
Posted Wednesday, June 22, 2011 5:36 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 1:03 PM
Points: 4,388, Visits: 9,510
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
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #1130070
Posted Wednesday, June 22, 2011 6:27 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, February 18, 2014 11:39 AM
Points: 11, Visits: 68
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
Post #1130081
Posted Wednesday, June 22, 2011 6:37 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 1:03 PM
Points: 4,388, Visits: 9,510
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
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #1130082
Posted Thursday, June 23, 2011 12:50 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:03 PM
Points: 42,485, Visits: 35,554
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 2008, MVP
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

Post #1130167
Posted Monday, July 25, 2011 12:54 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, February 18, 2014 11:39 AM
Points: 11, Visits: 68
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
Post #1147815
Posted Monday, July 25, 2011 12:58 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:22 PM
Points: 12,890, Visits: 31,849
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1147819
Posted Monday, July 25, 2011 1:11 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, February 18, 2014 11:39 AM
Points: 11, Visits: 68
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
Post #1147826
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse