FAQs - ApexSQL Log

ApexSQL Log supports:

SQL Server 2005 32-bit
SQL Server 2005 64-bit (x64)
SQL Server 2000 32-bit
SQL Server 7.0

For online transaction log reading to work operating system has to be 32-bit versions of Windows 2000 or higher or 64-bit versions of Windows XP x64 or higher. For Itanium (IA64) SQL Server 2000 and 2005 only experimental support is available.

ApexSQL Log does not use triggers but instead reads databases transaction logs to provide auditing trail.

ApexSQL Log has no performance overhead during audit trail capture since it is not involved in it at all - all it does is read transaction logs after the fact. This allows auditing to be performed during low load time or even off server. The only data that has to be captured in real time is additional login information (NT user name, application name and client host name) since this information isn't available in transaction log. This information is captured by ApexSQL Log's Connection Monitor which has minimal overhead and after new login is made.

No, ApexSQL Log cannot audit SELECT statements as these are never logged into transaction log.

ApexSQL Log provides command line interface with full access to all features available in graphical user interface. It was designed from ground up with batch processing in mind and can be run fully unattended.

With ApexSQL Log you can export auditing results in XML and CSV formats or you can export them as BULK or SQL scripts in order to import them to a SQL database.

ApexSQL Log is licensed per-instance of SQL Server. When a license is bought it allows activation of one SQL Server instance which in turn allows connecting to that particular instance from ApexSQL Log client. On the other hand, ApexSQL Log client can be installed on any PC. For more information on server activation, please check the Server Activation section of the Getting Started section of this help file.

ApexSQL Log evaluation version can be installed on any number of server instances and users are not restricted in using it with only predetermine databases. However, evaluation version does have two important limitations:

1. Trial is limited to 30 days for any given server instance.
2. Recovery Wizard is limited in recovering only every 10th row of total rows available for recovery.

The easiest way to active ApexSQL Log is through Automatic Activation. To activate manually, click here.

ApexSQL Log's installation requirements are fairly small, and their overall effect on performance is negligible. At the file level, ApexSQL Log installs 4 components, all in the SQL Server instance's Binn folder. These are:

1. ApexSqlLogXprocs.dll - the DLL containing all extended procedures used by ApexSQL Log.
2. ApexSqlConnectionMonitor.exe - the executable for Connection Monitor component of ApexSQL Log.
3. LNHF.exe - the executable used by ApexSQL Log extended procedures for task done externally to SQL Server process.
4. LNHF.sys - the service executable used by ApexSQL Log extended procedures.

At the server-object level, there are a total of 5 procedures and one table. There are four extended procs included in the dll file above - xp_ApexSqlLogMonitor, xp_ApexSqlLogMonitor_Info, xp_ApexSqlLogMonitor_Stop, and xp_ApexSqlLog. There is also one stored procedure - master..sp_ApexSqlLogMonitor_Start - and one table (used for storing login information as part of ApexSQL Log's Connection Monitor) - msdb..APEXSQL_LOG_LOGIN.

For remote installation to correctly work ApexSQL Log needs the user running it to be logged on the remote server with the right set of permissions. This can work either through NT domain or a workgroup but the important thing is that the set of permissions assigned to the user installing server components is adequate. On SQL Server side the user should be part of sysadmin fixed server role since ApexSQL Log needs to install its extended stored procedures. If remote installation is the problem, the easiest way to install server components is to install the client on the server and then connect to SQL Server locally.

There are two ways to uninstall server side components: from GUI or from CLI. In GUI, go to server tree, right-click on the server from which you wish to remove the components and then choose "Uninstall Server-Side Components..." option from the pop-up menu. In CLI, simply type name of the server together with "/uninstall" switch.

Just uninstalling ApexSQL Log client will not remove any server side components. Prior to that you will need to uninstall server side components from the client itself, from all the servers you wish to do so.

The simplest way to verify is to check that the following files have been removed from server's Binn folder: ApexSqlLogXprocs.dll, ApexSqlConnectionMonitor.exe, LNHF.exe and LNHF.sys. You may also want to check that the following procedures (extended and stored) have been removed from server's master database: sp_ApexSqlConnectionMonitor_Start, xp_ApexSqlLog, xp_ApexSqlConnectionMonitor, xp_ApexSqlConnectionMonitor_Info, xp_ApexSqlConnectionMonitor_Stop. If all these elements have been removed - server components have been removed.

We strongly recommend that you use ApexSQL Log to uninstall server side components. But if you just have to uninstall them manually, then you can do the following. First run the following query on your server:

IF EXISTS (SELECT *
           FROM   MASTER.DBO.SYSOBJECTS
           WHERE  NAME = 'xp_ApexSQLConnectionMonitor_Stop')
  EXEC MASTER.DBO.XP_APEXSQLCONNECTIONMONITOR_STOP

IF EXISTS (SELECT *
           FROM   MASTER.DBO.SYSOBJECTS
           WHERE  NAME = 'xp_ApexSQLLog')
  EXEC MASTER.DBO.SP_DROPEXTENDEDPROC
     'xp_ApexSQLLog'

IF EXISTS (SELECT *
           FROM   MASTER.DBO.SYSOBJECTS
           WHERE  NAME = 'xp_ApexSQLConnectionMonitor')
  EXEC MASTER.DBO.SP_DROPEXTENDEDPROC
     'xp_ApexSQLConnectionMonitor'

IF EXISTS (SELECT *
           FROM   MASTER.DBO.SYSOBJECTS
           WHERE  NAME = 'xp_ApexSQLConnectionMonitor_Stop')
  EXEC MASTER.DBO.SP_DROPEXTENDEDPROC
     'xp_ApexSQLConnectionMonitor_Stop'

IF EXISTS (SELECT *
           FROM   MASTER.DBO.SYSOBJECTS
           WHERE  NAME = 'xp_ApexSQLConnectionMonitor_Info')
  EXEC MASTER.DBO.SP_DROPEXTENDEDPROC
     'xp_ApexSQLConnectionMonitor_Info'

IF EXISTS (SELECT *
           FROM   MASTER.DBO.SYSOBJECTS
           WHERE  NAME = 'xp_GetLiveLogStatus')
  EXEC MASTER.DBO.SP_DROPEXTENDEDPROC
     'xp_GetLiveLogStatus'

IF EXISTS (SELECT *
           FROM   MASTER.DBO.SYSOBJECTS
           WHERE  NAME = 'sp_ApexSQLConnectionMonitor_Start')
  EXEC MASTER.DBO.SP_PROCOPTION
     'sp_ApexSQLConnectionMonitor_Start' ,
     'startup' ,
     'false'

IF EXISTS (SELECT *
           FROM   MASTER.DBO.SYSOBJECTS
           WHERE  NAME = 'sp_ApexSQLConnectionMonitor_Start')
  EXEC MASTER.DBO.SP_EXECUTESQL
     N'DROP PROCEDURE sp_ApexSQLConnectionMonitor_Start'

DBCC APEXSQLLOGXPROCS ( FREE )

The result of this script should be something like this:

Stopping ApexSQL Connection Monitor...
ApexSQL Connection Monitor stopped
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

This means that Connection Monitor has been stopped, the all the extended and stored procedures were dropped successfully and ApexSQL Log Xproc DLL has been unloaded from SQL Server's memory (which allows us to delete it).

After all objects are dropped from server, the second and final step is to delete binaries of all ApexSQL Log components from server's Binn directory. These binaries are:

1. ApexSqlLogXprocs.dll
2. ApexSqlConnectionMonitor.exe
3. LNHF.exe
4. LNHF.sys

If this step finishes successfully as well - there is nothing left of ApexSQL Log's server side components on your server.

We support all x86 32-bit editions of SQL Server 7.0, SQL Server 2000 and SQL Server 2005. The only feature missing from SQL 7.0 support is Connection Monitor, which uses some SQL Server 2000/2005 specific features.

ApexSQL Log can only show information available in the SQL Server's transaction logs and their backups. For UPDATE operations SQL Server, in general, does not store full row information so ApexSQL Log has to work for it to get all the data. This process usually consists in going back through loaded transaction logs in search of update row's INSERT operation. Once the corresponding INSERT is found, ApexSQL Log can reconstruct the updated operation and show before/after values. However, sometimes this corresponding INSERT isn't available (e.g. transaction log backup containing it was not fed to ApexSQL Log, it preceded switch to FULL RECOVERY mode and so on) in which case the only thing ApexSQL Log can show is the binary data that was written to SQL Server - which is what you are seeing.

What you are seeing are the records that fit both the filter criteria as defined when you use the Operations filter before accessing the log, and the operations that fit the scope of the application itself. ApexSQL Log shows only DML operations (INSERT, UPDATE and DELETE) made on tables (user or system). There are many other operations that are SQL Server system-level operations that are logged in the transaction log but do not influence user data or schemas and these operations we do not display in the results grid.

After switching from SIMPLE to FULL RECOVERY model, you must do a full database backup in order for SQL Server to stop truncating transaction log. Without this action ApexSQL Log, in general case, cannot find any transactions to display since they are being continually overwritten (due to continued truncation of transaction log). For more details please see "Switching Recovery Models" article in SQL Server's Books-On-Line.

Full database backups made by SQL Server only include the part of transaction log that was active at the moment when the backup was initiated plus operations that were logged during the database backup itself. So even though database backup restoration process creates transaction log files with their original sizes, the transactions prior to the backup itself are not restored (except for the active part which in general case is minimal). To fix this problem, use relevant transaction log backups with the restored database instead of database's online logs.

Once the log or set of logs for a particular database has been opened, you can export a SQL Script reflecting the whole set of changes to the database by using the 'Export DDL' function under the 'File' menu. The two options (UNDO and REDO scripts) will both contain the whole known set of DDL operations on the database, in their appropriate form.

ApexSQL Log works both with transaction log backup files and with transaction log files. It also accepts data backups since a backup file can contain both database pages and transaction log operations. If such a file is given to ApexSQL Log, it will try to find any transaction log backups within it. Also, ApexSQL Log accepts detached transaction logs. Furthermore, if the transaction log info is spread across multiple files of these types, they can be selected as a group and make the whole set of data across all the files made available to ApexSQL Log.

No, it's not necessary. If you wish to access a detached log file or a backup that is on server all you need to do is provide its network (shared) path. Notice however that this works only for detached log files or log backups. Online logs (those used by SQL Server at the time) must be accessed through Live Log which is part of ApexSQL Log's server-side components.

ApexSQL Log analyzes SQL Server's transaction logs and log backups and thus allows performance-free auditing of all changes in a database. It does not allow the administrator to choose which fields to audit because SQL Server does that job for us at all times and for all fields in the database (with exception of text/ntext/image fields). Later, during auditing process, users can filter the auditing result by tables, users, and so on.

Sometimes, SQL Server logs UPDATE as DELETE/INSERT pair. If we were to remove history for INSERTs (or DELETEs), historic records could not be analyzed. We have considered merging DELETE/INSERT pairs so that they are shown as one UPDATE, but the problem is that sometimes one cannot discern between genuine DELETE and INSERT vs. UPDATE logged as DELETE/INSERT pair. Therefore, it was decided to leave them as DELETE/INSERT pairs.

ApexSQL Log reads and displays everything from transaction logs without importance of whether the data has been truncated since or not. Truncating the log does not reduce the physical size of the log; it just marks inactive virtual logs as truncated so that they become available for overwriting. Because ApexSQL Log works on the lowest level by directly reading the log files, it circumvents this logical truncation and thus can display even the data that was previously truncated (that was not overwritten in the meantime). This is useful because it allows users to audit the data that was changed even before installing ApexSQL Log and in some cases even before switching to FULL RECOVERY mode ('trunc. log on chkpt.' turned off on SQL7).

ApexSQL Log has many filters, including begin/end time filters. Using them, one could filter out all operations that do not match, say, a certain point in time.

'Explicitly' as in go to a specific byte/region, no. 'Explicitly' as in show me only transactions in this time range or for this user, yes. Again, ApexSQL Log has many great filtering options (including field filtering with comparison operators).

In the current version of ApexSQL Log, auditing of changes to text/ntext/image fields is not supported. We are working on this feature and it will probably be released with the next major version of ApexSQL Log.

here is no limit on the number of rows shown. Have you tried adding more backups to the source? ApexSQL Log is not showing all log records but only those that belong to a committed transaction that had DML statements. Also, by default, all changes to system tables are not shown. Try changing the filter and/or adding more backups.

While it is not possible to run ApexSQL Log as a background job, it can be run from the command line through the ApexSQLLog.com program. Command line was specifically developed to address these sort of problems where ApexSQL Log needs to be run automatically without human supervision or needs to be integrated into some batch processing. In your case, you would schedule ApexSQLLog.com to run at certain times during the night. For each of the databases, you would create one scheduled job and have it analyze the backup and produce the output you wish (XML/SQL Script and even UNDO and REDO). Through command line, one could also filter tables, users, and time ranges.

ApexSQL Log only knows the information that is given to SQL Server. There is no way around this issue from SQL Server's or ApexSQL Log's perspective. This issue would have to be addressed from within the web applications by altering how they login. SQL Server simply records the information it has and if one login is used then that's all SQL Server knows.

Table is marked as unknown when it has been dropped since the operation for it was logged. In this case ApexSQL Log displays table name as "UNKNOWN(ID)" where ID is the identification number of the table. You can solve this problem by recreating the table and then mapping in Log this new table to old ID. Be warned however that physical schema of the new table may be different from the physical schema of the old table (which can happen even when they are logical equivalent). In this case Log will still try to use the new schema but results may not be complete or correct.

'Split' transactions are caused when page splitting occurs. This is a normal occurrence with SQL Server and happens when data page is filled so much that on next page update, SQL Server has to split it in two and divide the rows to both. In any case, if you are seeing 'Split' transactions, then filter them out (Miscellaneous option in the Log Filter dialog box). In general case, they are of no interest for auditing purposes.

User (-1) followed by 'implicit' description is most probably a transaction that was run by SQL Server itself. We would have to analyze the particular case but because users in sysusers start from 0 upwards, '-1' should be SQL Server. What are you seeing in the window below? For these operations, ApexSQL Log should not show any details.

Each operation is associated with corresponding transaction properties, including begin time, commit time, SQL user ID, SPID, and description of transaction. In addition, if ApexSQL Log's Connection Monitor is running on target server, each transaction could also be associated to NT user name, host name, and application name.

Yes, absolutely. ApexSQL Log was designed from ground up to offer non-invasive reading of online transactions logs and of transaction log backups. Furthermore, ApexSQL Log needs the database to be active in order to retrieve necessary information that is not available in the transaction log (for example, table meta-data).

The table name is available always if the auditing is done with matching transaction log and database and if the table hasn't been dropped since the operation in question. If the table was dropped its name will appear as UKNOWN(id) where id is the table's id as recorded in the transaction log. If the table has been re-constructed after the drop then it is most likely has a different id and thus ApexSQL Log cannot retrieve its meta-data (name and columns names and types). In this case you can use "Old Table ID Mapping" feature to map old table id to re-create table id.

Operation type is always available as this is recorded in the transaction log.

Column values are always available provided that:

1. Corresponding table's meta-data is available and matches the meta-data as it was at the moment of operation.
2. ApexSQL Log has access to all transaction log data necessary to reconstruct before-after states of the row. In the case of INSERT and DELETE operations this is always true but under some circumstances, auditing of an UPDATE operation may fail due to the lack of necessary data. If that is the case, column values are not shown, but raw changes made to the row are shown. Under some circumstances (most notably for strings), this raw data may help user to understand what was updated.
3. The column is not of BLOB type.

In the log view, select the rows you wish to examine in Excel. Click the File menu, point to Export Selection As, and then click CSV File to export the rows as a comma separated values (CSV) file. Open the CSV file from Excel.

Use the multiple row selection (holding the Ctrl key while selecting with mouse). Next, click the File menu, point to Export Selection As, and then click the required command.

No, on both accounts. Regarding database footprint, ApexSQL Log's own footprint is minimal or non existing depending on whether Connection Monitor is installed. Connection Monitor inserts one row per connection into the msdb.dbo.APEXSQL_LOG_LOGIN table.

If you decide to use BULK or SQL export of audited data, however, then the footprint will go up accordingly. In that case, you can decide to execute the resulting scripts on another server and not the one being audited. Some customers run their auditing solutions by running ApexSQL Log through CLI in batch, and then moving resulting export files to their auditing server. Even in this case, you decide what you want to audit; ApexSQL Log does not decide that for you, so you can gauge the footprint.

Regarding performance, there are two parts to the answer. First, the only active part of ApexSQL Log is Connection Monitor. Its performance impact is negligible. Furthermore, Connection Monitor is installed only if Live Log component is installed and it can be easily disabled if so desired. So again, minimal to nonexisting impact.

Another part of the answer is what happens when an online transaction log is being analyzed. In this case, ApexSQL Log has to read the online transaction log, which does have an impact on the performance of the server. This impact depends on I/O capabilities of the server in question. If this is undesirable, then it can be avoided by analyzing transaction log backups or even detached transaction logs.

ApexSQL Log is a completely passive batch tool except for Connection Monitor, which constantly monitors new SQL Server connections.

There are no known issues with reading active logs. This feature is not recent; it has been available since version 0.90 (which was BETA) released in July 2003. It was redesigned in September 2004, however, to improve stability and fix all the design problems.

Reading of active logs is supported only on SQL Servers running on Windows 2000, XP, and 2003. Clients can be run on other operating systems, however, such as Windows 98 and Windows NT.

One has to be an Administrator to install Live Log components. This is because this involves installation of a small Windows service that Live Log uses. Furthermore, the user account running SQL Server service has to be authorized to connect back to SQL Server.

There are no known issues with running multiple copies of ApexSQL Log. Furthermore, ApexSQL Log was designed for this so it never opens exclusive handles on log files that it is analyzing. This means that you can simultaneously run multiple instances of ApexSQL Log against the same database

For technical reasons ApexSQL Log needs server name as it is known by SQL Server and in order to obtain that name it uses T-SQL @@SERVERNAME function. However, when server machine name is changed after the installation of SQL Server, @@SERVERNAME function continues to return the original machine name which leads to ApexSQL Log client being unable to connect to server and several other problems.

To fix the name returned by @@SERVERNAME do the following:

1. Execute on master database these two queries:
1. EXEC sp_dropserver <old_name>
2. EXEC sp_addserver <new_name>, 'local'
2. Restart SQL Server service.

ApexSQL Log has no automatic failover so it has to be redirected manually. If working with the Command Line interface the recommendation is to install ApexSQL Log with the same batch files on each server in the cluster that requires auditing. When the failover happens, log auditing will need to be started manually. The alternative is to install ApexSQL Log on one PC outside of the cluster and have it audit it through cluster name. When the failover happens, it will get switched to the other node together will all other users.

You can turn off the Connection Monitor and delete all previously recorded logins by executing the following script:

-- Turns off the Connection Monitor.
USE MASTER

EXEC XP_APEXSQLCONNECTIONMONITOR_STOP

-- Prevents Connection Monitor from automatically starting on server startup.
EXEC SP_PROCOPTION
   'sp_ApexSQLConnectionMonitor_Start' ,
   'startup' ,
   'false'

-- Disables Connection Monitor from being started in the future.
EXEC XP_APEXSQLCONNECTIONMONITOR_DISABLE

-- Deletes old logins so that they don't invalidate results on new transactions.
USE MSDB

TRUNCATE TABLE APEXSQL_LOG_LOGIN

This will in general not affect ApexSQL Log's basic ability to read and process transaction logs. The Connection Monitor is a value-added feature that tracks and records information about individual logins that SQL Server itself does not do. By turning this off, you will lose specifically all information on all NT logins (in SQL2000 only) and all information on the connecting client application and client host info (in both SQL2000 and 2005 servers). You will retain all information normally tracked by SQL Server in all instances.

Connection Monitor stores one row per connection in msdb.dbo.APEXSQL_LOG_LOGIN table. ApexSQL Log does not clean this table but a simple script deleting rows older than say 30 days should work. However, once that data is deleted, ApexSQL Log won't be able to retrieve NT user, client host nor application for old transactions (older than 30 days). We would recommend to incorporate archiving of the connection data along with the transaction log backup archival.

NT user name, application name and client host data is not found in transaction log. Instead, ApexSQL Log captures that data through its Connection Monitor and then later during auditing process this is matched with transaction log data. When database is moved from one server to another then ApexSQL Log cannot find the correct captured login data and invalid data (or none at all) is shown. This is a known problem and we will fix it in the future releases of the product.