Corrupt Database - 55,000 Records Lost 1

[chunter33]

Hi there, shortly I will be investigating an issue regarding an Access 2007 database which is supposedly "corrupted", and some time last week it lost 55,000 records. The details of what went wrong and caused the corruption are very unspecific, all I know is that it happened at some point between Wednesday, and Friday and at one point a compact was performed.

This is a database that is used in a multi-user enviroment. As far as I'm aware, it's not possible for 55,000 records to be lost over a network. Even if some large operation was halted that was selecting 55,000 records from a table, because of the 4k page-locking packet size limit there's no way that as many as 55,000 should be lost. Apparently those 55,000 records took up about 57MB.

Also, from the sounds of things, it's being labeled as a "corruption" but there isn't actually any sort of corruption message... I'm more inclined to believe that a user accidently ran a delete query or was getting a little too friendly with the Ctrl + X keys.

Thoughts? Comments?

 

[alvechurchdata]

I'd agree that it sounds like the result of user action rather than corruption. Corruption normally trashes everything - I've not seen it neatly take some records and leave the rest untouched.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[chunter33]

Are there any other alternatives that don't involve a corruption or devious users? After looking at the database in question, it's actaully a little more than 53k records that are missing - and it's all primarily from 1 table. If you follow the autoid column down, the "corrupted" database gets to record 794157 and then stops completely. Of course, it's supposed to go 53k records higher so it shouldn't end until around autoid 847635.

There is only one mechanism in place for the user to delete records (outside of manually going into the table and deleting 53k records outright) and it's impossible to make the table's records abruptyl end on the 794157th record like it is through that mechanism.

Is it possible that there was a botched backup or something without causing any corruption messages? Again, I'm very keen on the thought that it was the doing of a user mucking around in tables, I would just like to be absolutely certain and explore all possibilites before I make a conclusion.

 

[jedraw]

Just a point about autonumbers, if that is how your autoid is defined -- they aren't necessarily sequential, they are supposed to be unique. I know it's a fine point but for what it's worth you could be missing a sequence number(s), even in a "perfectly good" table. I'm not suggesting that's the case here; you know that records are missing.

It certainly sounds like they have been "deleted" perhaps through devious user, but Allen Browne has a number of points on corruption that "could/might??? play some role.

http://allenbrowne.com/Bug-OrderBy.html

http://allenbrowne.com/bug-DeleteOnCompact.html

http://allenbrowne.com/ser-47.html

 

[chunter33]

Sorry, let me clarify. They aren't actually autoid's, just sequential Id's starting from 1 to uniquely identify records.

Thanks for those links, I'll take a look at them.