What else can I do to find missing data?

[Former Member]

We have some "lost" data and my usual techniques of finding it aren't working. SQLA 16.0.0.1915. Straight client-server app, no MobiLink. A user brought me a printout from May 14 showing 2 lines of data. Running that printout today only contains 1 line of that data. My usual high-level "audit trails" didn't show any deletes. So I did dbtran -d -t of all the transaction log files from that day until today (we do twice a day dbbackups with -r option). I found the insert transactions in the May 14th log file as expected. That gave me the PK for the lost record (a BIGINT Global Autoincrement). I ran an ISQL select for that PK just to make sure the row isn't there and suffering from some report bug. I did a Windows gnu grep search of all the files for that PK value. The ONLY result was the original insert. We run dbvalid every night on the database, and no errors reported.

So I am stumped! And would surely appreciate any further ideas. Thanks.

[MarkCulp]

Did you look for TRUNCATE TABLE statements or UPDATE TABLE statements that do not specify a WHERE clause (i.e. all rows in the table get updated?)

[former_SQLA_member1694878]

Do you have a full backup before the INSERT statement? If you have complete history of the transaction logs from that backup to the present, you could try applying them to see if the disappearing 'reproduces'. If it does, then we could hopefully narrow it down to a bug or a bad UPDATE/DELETE/TRUNCATE statement.

[Breck_Carter]

Is it possible the May 14 report showing 2 lines of data was showing data that had been inserted but not yet committed, and the connection doing the insert subsequently failed or issued a rollback?

Perhaps dbtran -a will help.

[Former Member]

I did check the whole transaction: Start Tran, Insert parent record + Insert 2 child records + commit, all on same transaction number.

It is "selective" data loss: out of roughly 300+ sets of records inserted, there appears to be approximately 35 sets which have lost part of the child data. So I can't think a TRUNCATE is the culprit. (I occasionally use a TRUNCATE in a conversion/data load script but I don't think there is a single such command in my app). That's why I thought I would find my problem by translating the trigger actions and finding some less-obvious bug. I verified all the related tables have FKs with Update Restrict and Delete Restrict.

My app (PowerBuilder) allows deletion of all but one child record per header. All of the loss, that I am AWARE of so far, amounts to one child record still present. So I dug into the log files expecting to find deletes that were missed by my higher level audit trail, or even possibly mischievous deletes.

We do have daily backups, so Mikel's idea seems like what I need to work on next.

Thanks for some good ideas.

[MCMartin]

If you still have the backup from May you could try to narrow down, when the record got "lost"

[Former Member · Accepted solution]

The mysterious part of my problem is solved! Using Mikel's suggestion, I isolated the problem to a half day. Editing the log file (with Wordpad) instantly revealed the delete for that record key. While I can find no published limitations on file sizes with the grep program I was using, it obviously did not parse this 258mb log file to find this entry almost at the end of the file. (Let me see: freeware plus the IT time to restore a lot of tape backups plus my time to isolate the particular database copy... that adds up to quite a few bucks!!)

Now to figure out what really was going on to delete the records...

Thanks again for the suggestions everyone.

After researching another issue last week which caused me to read some posts on StackExchange, quite a few of which were not helpful and often down right rude, I appreciate once again this forum and all of you who contribute respect, as well as really helpful information, to all of us! Thanks!!

[former_SQLA_member1694886 · Accepted solution]

On Windows, I've found 010 Editor very useful with to parse large SQL Anywhere (4-5 Gbs) log files. You could also slice huge files into more manageable chunks and take it from there

[VolkerBarth · Accepted solution]

Well, you might also want to appreciate the fact that SQL Anywhere comes with the DBTRAN tool out-of-the-box... I have always felt glad that the database's activities can easily be traced afterwards with the help of a translated log, and AFAIK most other database systems do not provide such a tool (or not as a built-in facility but possibly by a third party tool)...