ApexSQL Log doesn’t show/recover all transactions

Applies to
ApexSQL Log

Summary
This article explains why ApexSQL Log shows only some of the transactions you expect to see and how to show/recover all of them.

Symptoms
You are using ApexSQL Log on a database where transactions are executed some time ago. You know that these transactions were executed, but ApexSQL Log doesn’t show them.

Cause

There are several reasons that can cause this behavior:

• The database that you are auditing is in the Simple recovery model and the transactions you want to see don’t exist in the online transaction log anymore. Only the most recent data will be displayed. In this scenario ApexSQL Log will inform you that selected database uses the Simple recovery model

  

• The database that you are auditing is in the full recovery model but you didn’t add the relevant transaction log backups
• The database that you are auditing is in the full recovery model, you have the transaction logs that contain the transactions you want to see, but you didn’t add all transaction log backups necessary for successful transaction reading
• You had used the ApexSQL Log default filter setting and inadvertently filtered out the data you were looking for

Resolution
ApexSQL Log can show/recovered all transactions only for databases in the full recovery model. If database that you are auditing is in the full recovery model, then proceed with the steps below to provide necessary transaction log backups to recover/show all of the transactions you are looking for:

1. Start ApexSQL Log

2. Connect to the database

   

3. In the Select data sources step, Add the continuous sequence of transaction log backups up to the point in time when the transactions you are looking for occurred. In this step you can also Add the full database backups created immediately before or after the continuous sequence of transaction log backups added previously.

   If a transaction log backup is missing from the continuous sequence of transaction log backups,

   

   ApexSQL Log will show inserted and deleted records for the database correctly, but not for the ones that were updated due to the fact that the full row history might be missing because of the incomplete transaction log chain.

4. In the next step of the wizard choose to Open results in grid or to Export/Undo, depending on the auditing or recovery task at hand

   

5. Use the Time range section in Filter setup options step to narrow down transactions to the ones that happened within a specified. Note that by default settings only last 24 hours from data sources will be shown

   

6. Use the Operations section in Filter setup to narrow down the transactions found in the data sources to a specific operation or a set of operations. By default settings, ApexSQL Log reads only the data (DML) operations and schema operations (DDL) are excluded and will not be shown. Schema operation can be included and grouped by the operation type into ALTER, CREATE, DROP, and OTHER

   

7. If possible, use the Tables filter and Advanced options to narrow down the results
8. Click Open results in grid or Export results to file