ApexSQL Log– tips n’ tricks

Applies to
ApexSQL Log

Summary
This article offers ApexSQL Log tips and tricks that will help users to increase the productivity and enhance overall experience when working with ApexSQL Log.

Description

Tip #1
When evaluating ApexSQL Log, it is recommended to use databases that will demonstrate the main ApexSQL Log features: undo script creation, redo script creation, auditing results to a grid, exporting results to various formats by utilizing all available SQL server features etc. Here is a script to create a database that will allow one to demo perform all of the aforementioned features.

Tip #2
During the trial period, it is not possible to export the results. Additionally, details are available on for every 10th operation.

View scripts for individual operations

Tip #3
It is highly recommended to add all of the transaction log backups that contain relevant information for the current auditing process in the Select SQL logs to analyze step of the wizard.

Adding all transaction log backups

Tip #4
ApexSQL Log will automatically scan for transaction log and full backups and mark them for auditing.
However, this feature might take a significant amount of time if the database has a large amount of backups. To turn off the automatic auto load of these backups, disable the feature in the options.

Turning off the automatic auto load

Tip #5
The most efficient way to increase the performance of ApexSQL Log is to utilize filters from the large variety of pre-built, custom filters, especially when dealing with very large log files, because it will narrow down the time needed for ApexSQL Log to read transaction logs file and increase auditing and exporting performance.

Increasing the performance of ApexSQL Log

Tip #6
ApexSQL Log will audit only last 24 hours of the transaction log data by default. To ensure whole transaction log has been read, chose the ‘whole transaction log’ filter, or set a custom time range to specify the precise time range.

Setting custom time range

Tip #7
Only the DML operations are audited by the default setting. The DDL operations need to be enabled in the filter setup. In addition, enabling the DDL operations has negative impact on the auditing performance in most cases, so it is recommended not to include DDL operations, to ensure best possible performance. When DDL operations are enabled, all DML operations on the system tables will be ignored.

Managing DML and DDL operations

Tip #8
Filtering out all tables that are of no interest in the current audit, as well as filtering out system tables (default setting) will narrow down the search and increase processing performance.

Tables filter setup

Tip #9
To pinpoint the long lasting transactions, utilize the transaction duration filter and set up a minimal transaction duration threshold.

Transaction options

Tip #10
Utilize the field values filter and add multiple conditions in order to easily pinpoint specific operations.
e.g. Set filter to ‘Greater than or equal to’ 15500 or ‘Is null’ to include only those values equal to null or greater than 15499

Field values filter

Tip #11
Additional columns can be shown in the results grid. To access these addition columns navigate to the ‘Columns’ tab in the filter setup step of the session wizard and select columns that will be displayed.

Columns shown in the results

Tip #12
All filters that were initially disabled in the session wizard will automatically be disabled in the grid view. They cannot be enabled from the grid view directly. In order to enable them, it is required to edit the session settings and enable these filters in the ‘Filter setup’ step of the wizard, which will result in ApexSQL Log having to re-read the transaction log.

Managing schema operations

Tip #13
The export to file feature in the main ribbon has global effect – it will affect full audit results and create an export for all results displayed in the grid, regardless of the grid selection. To export only specific (selected) operations, check them in the grid and use ‘Export checked as…’ option from the right click menu and select the export type in the options list.

Exporting options to file types

Tip #14
Instead of writing a CLI script manually, it is recommended to set up filters via the session wizard, and to select ‘Create batch file’ option in the last step of the wizard. The CLI script will be created automatically and can be simply copied or saved to a file.

Create batch file

Tip #15
It is possible to quickly filter out the results in the grid and further narrow them down without the need to re-read the whole transaction log by utilizing the grid filters. Set up the filter details in the ‘Grid filter’ pane and apply it directly from the grid.

Create batch file

Tip #16
In order to retrieve schemas and objects for the records that are shown as UNKNOWN in the Main grid, which occurs when transactions for the specific table are found in the transaction log, and the table with the specific ID does not exist in the database anymore use old table id mapping feature to map the new table to the old ID

Retrieving schemas and objects

Tip #17
Additional statistics for the audited results can be brought up by clicking on the ‘Checked/Excluded’ button in the lower right corner.

Additional statistics