This article explains where and how to find specific information about SQL transactions in ApexSQL Log.
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
The most commonly used GUI element in ApexSQL Log for the transaction information is the main application grid.
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:
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:
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.
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:
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:
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:
The information consists of operation types, dates, user names, LSNs, and the row field names.
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.
The script also contains the information about the operation that will be rolled back (or replayed) in the script header comment.
The Transaction information pane provides the information already shown in the main grid for the selected 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.
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.