SQL transaction details in ApexSQL Log

Applies to
ApexSQL Log

Summary
This article explains where and how to find specific information about SQL transactions in ApexSQL Log.

Description

There are several methods in ApexSQL Log for providing information about transactions from SQL Server database transaction logs. The information can be either exported for later analysis or reviewed using the application GUI.

The following application GUI elements provide specific transaction information:

  • The main application grid
  • The Operation details pane
  • The Row history pane
  • The Undo script pane
  • The Redo script pane
  • The Transaction information pane

Main grid

The most commonly used GUI element in ApexSQL Log for the transaction information is the main application grid.

Main application grid in ApexSQL Log, used for SQL transaction details

By default, the grid shows all columns once the information is read from data sources:

  • Begin time
  • End time
  • LSN – the log sequence number which uniquely identifies every record in the SQL Server transaction log
  • Object – the name of the object affected by the operation
  • Operation – the DML or DDL operation executed on the database object
  • Schema – the name of the schema that the affected object belongs to
  • Transaction ID – the ID of the transaction executed on the database
  • User – the SQL Server login name of the user who executed the operation

You can select particular columns to show in the grid instead of default ones by using the Select columns option on the View tab in the menu:

Using the Select columns option on the View tab in ApexSQL Log

Additional available columns (Columns shown in the results) can be enabled in the Columns tab of Additional options in the Filter setup dialog of the session wizard:

Filter setup dialog in ApexSQL Log session wizard – using Columns tab

Quick tip icon

Quick tip:

If you need any of these additional columns, enable them before completing the session wizard. Columns are available in the Select columns dialog only if they are previously checked in Columns shown in the results.

Operation details

The Operation details pane shows additional information for the SQL transaction selected in the grid. The information is specific for each operation type.

The information for UPDATE operations shows all table fields for the particular record, their types, old, and new values. For the updated column, the cell with the new value is highlighted and bolded:

The Operation details pane, showing additional information for the selected SQL transaction

Similarly, INSERT and DELETE operations provide appropriate information, but without the New value column.

As for DDL operations, (e.g. DROP TRIGGER), the Operation details pane shows information describing additional SQL transactions on system objects:

Operation details pane - showing additional SQL transactions on system objects

Row history

If a DML operation is selected in the grid, the Row history pane will provide historical information for all previous changes on the particular table data row:

Row history pane, providing historical information for selected DML operation

The information consists of operation types, dates, user names, LSNs, and the row field names.

Undo/Redo script

Both Undo script and Redo script panes provide appropriate T-SQL scripts for a single selected operation in the main grid. A script is provided for DML and DDL operations. It can be manually copied to the clipboard and pasted to SQL Server Management Studio (or any other database management tool) for execution.

Undo script generated using ApexSQL Log

The script also contains the information about the operation that will be rolled back (or replayed) in the script header comment.

Transaction information

The Transaction information pane provides the information already shown in the main grid for the selected transaction.

Transaction information pane provides the information for the selected SQL transaction

The additional information is:

  • Operation (Committed, Aborted, Running, or Unknown)
  • Parent schema
  • Parent object (e.g. a table if the transaction was related to a trigger)
  • User ID
  • Description (e.g. the MyDescription value if the following command is executed on the database: BEGIN TRAN MyTran WITH MARK ‘MyDescription’)
  • Transaction duration
  • SPID (the SQL Server process ID which is created every time an application connects to SQL Server)
  • Page ID (internal SQL Server information, related to DML operations only)
  • Slot ID (internal SQL Server information, related to DML operations only)
  • Previous LSN

Filtering and searching

In addition to the information shown, ApexSQL Log offers advanced filtering of the grid using the Filter grid pane. The grid information can be filtered by the time frame, DML and DDL operations, and users who executed operations.

Using the Grid filter pane in ApexSQL Log

Quick tip icon

Quick tip:

The Find option, on the Home tab in the menu, provides a dialog for searching throughout the grid, Operation details, or Row history of all shown transactions.