CODEWITHSUNDEEP
sqlsql-server-2008vb.net-2010
rev_dev_01
rev_dev_01

Reputation: 540

Missing records in SQL server tables

I have a database in place with a client that seems to lose data overnight. They enter records and exit the system, and then claim to not be able to find them again the next day.

The ID numbers in the Primary Key Index of the affected tables do seem to have gaps in, when they should be auto-incremented and continuous. The client does not have the facility to delete records, so there seems to be an issue.

I have tried using DBCC CHECKDB and DBCC REINDEX but the records do not re-appear and the issue continues.

On exit from the VB.NET 2010 app, I use the following to write the record for each table:

Me.binds_Tablename1.EndEdit()
Me.binds_Tablename2.EndEdit()
TableAdapterManager.UpdateAll(Me.Dataset_1)

This system has worked fine for 2 years, but is now playing up. Could database corruption be the issue?

Upvotes: 9

Views: 2177

Answers (3)

rev_dev_01
rev_dev_01

Reputation: 540

This now appears to be sorted - the client was running a mirrored hard disk, one of which was starting to fall over with SMART error reports. Since this was replaced, the problem has not re-occurred.

Thanks for the help!

Upvotes: 0

SQLMenace
SQLMenace

Reputation: 135021

since this just started, is it possible that they are entering 2012 somewhere or it is related to the year 2012, maybe this value does not exist in a lookup table, the transaction gets rolled back

When a transaction rolls back the identity value is NOT reused, this is why you see gaps, you need to find out why you have rollbacks

Upvotes: 3

Phil Klein
Phil Klein

Reputation: 7514

If memory serves, the TableAdapterManager.UpdateAll() method wraps the updates in a transaction. Auto-increment fields increment outside of transactions, so I would guess that some of your transactions may be rolling back.

Upvotes: 2

Related Questions