How to get a complete audit trail for UPDATE transactions

Applies to
ApexSQL Log

Summary
This article explains how to get all UPDATE transaction records when audited data isn’t shown with messages including “Data unavailable”, “No data available for selected operation” and “Record change history could not be constructed”.

Symptoms
When selecting INSERT and DELETE transaction records in the main grid, ApexSQL Log shows audited data correctly, but not for the UPDATE transactions.

For the UPDATEs, ApexSQL Log shows:

Operation details tab: ‘No data available for selected operation’
Row history tab in Row information column: ‘Operation: 0 – Data unavailable’
Undo script and Redo script tabs: ‘RECORD CHANGE HISTORY COULD NOT BE CONSTRUCTED’ message is shown for the updated records

Cause
Unlike INSERT and DELETE statements, which are completely logged in the database transaction log, UPDATE statements are logged minimally. When SQL Server logs UPDATE operations, it doesn’t log complete before and after row states. Only the gradual change that occurred to the row will be logged. For example, if a word “lock” was updated to word “block” SQL Server will only log an addition of letter “b”. This is not enough to show before and after states of the row. To resolve this, ApexSQL Log has to reconstruct the context in which the change occurred from the rest of the transaction log and/or backup and online database data.

ApexSQL Log achieves this by using various, mutually complementary raw state reconstruction techniques. One of those techniques is reconstructing using the full log chain. A log chain is a continuous sequence of transaction log backups. It starts with a full database backup followed by all subsequent log backups up through the auditing point. If it becomes broken, only the transactions in the logs up to the last backup before the missing one can be shown with full information (e.g. a schema and object name, or a row history).

Providing the full log chain, with the most recent full and differential backups, allows ApexSQL Log to reconstruct the UPDATE context starting from the updated row’s state at the time of the last backup and reconstructing all the operations that affected since then.

If the original INSERT statement, or any subsequent UPDATE statement cannot be found, rather than providing partial and potentially incorrect information, ApexSQL Log displays the “RECORD CHANGE HISTORY COULD NOT BE CONSTRUCTED” message and doesn’t generate UNDO and REDO scripts.

Resolution
Reconstruction of the UPDATE transaction is possible only if the full database backup that contains the old or the new value of the updated record is provided. Another option is to add a transaction log backup or a detached transaction log that contains the INSERT or DELETE statement of the updated record. We recommend adding a full database backup if you are not certain when the record was inserted or deleted.

Proceed with the steps below to provide enough info to successfully audit updated records:

Start ApexSQL Log
Connect to the database
In the Select SQL logs to analyze step, Add the transaction log backup chain up to the auditing point
In the same step, Add the full database backup that starts the transaction log chain, or the full database backup created immediately after the last transaction log backup in the chain
Use the Time range section in Filter setup options step to narrow down transactions to the ones that happened within specified
Click Open results in grid
All transaction records will be shown in the main grid, according to the provided sources and filter settings