Suddenly Data Loss

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » Data Corruption » Suddenly Data Loss

Suddenly Data Loss

Rate Topic

Display Mode

Topic Options

Author

Message

Nemachtiani

Posted Wednesday, June 22, 2011 4:16 PM

Grasshopper

Group: General Forum Members
Last Login: Friday, April 19, 2013 12:17 PM
Points: 11, Visits: 67

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

Jeffrey Williams 3188

Posted Wednesday, June 22, 2011 5:36 PM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Friday, May 17, 2013 11:21 AM
Points: 4,317, Visits: 9,216

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

Nemachtiani

Posted Wednesday, June 22, 2011 6:27 PM

Grasshopper

Group: General Forum Members
Last Login: Friday, April 19, 2013 12:17 PM
Points: 11, Visits: 67

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

Jeffrey Williams 3188

Posted Wednesday, June 22, 2011 6:37 PM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Friday, May 17, 2013 11:21 AM
Points: 4,317, Visits: 9,216

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

GilaMonster

Posted Thursday, June 23, 2011 12:50 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:00 PM
Points: 37,744, Visits: 30,024

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

Nemachtiani

Posted Monday, July 25, 2011 12:54 PM

Grasshopper

Group: General Forum Members
Last Login: Friday, April 19, 2013 12:17 PM
Points: 11, Visits: 67

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

Lowell

Posted Monday, July 25, 2011 12:58 PM

SSChampion

Group: General Forum Members
Last Login: Today @ 10:11 AM
Points: 11,648, Visits: 27,764

Nemachtiani (6/22/2011)

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

Nemachtiani

Posted Monday, July 25, 2011 1:11 PM

Grasshopper

Group: General Forum Members
Last Login: Friday, April 19, 2013 12:17 PM
Points: 11, Visits: 67

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 »

Permissions