FAQs - ApexSQL Log

ApexSQL Log is licensed per-computer on which SQL Server is installed. When a license is bought it allows activation any SQL Server instance installed on computer which in turn allows connecting to instances from ApexSQL Log client. A computer is considered any instance of operating system so if you have a physical computer running 8 VMs each with its own SQL Server then you will need 8 licenses. On the other hand, ApexSQL Log client can be installed on any PC without any limitation. 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:

• Trial is limited to 14 days for any given server instance
  
  
• Generation and export of UNDO/REDO scripts is limited to every 10th operation.

ApexSQL Log in SQL Server cluster is licensed per active node of the cluster. For example, if you have an active-passive cluster, then you will need only 1 license. However, if you have an active-active cluster, then you will need two licenses. Additional licenses would have to be purchased for every passive node that's converted to active node.

Use Automatic Activation. To activate manually, click Here. Remember, you must activate ApexSQL Log on the SQL Server that you want to view the transaction log of.

ApexSQL Log supports:

SQL Server 2008 32-bit (x86), 64-bit (x64)
SQL Server 2005 32-bit (x86), 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, 2005 and 2008 only experimental support is available.

ApexSQL Log does not use triggers; instead, it uses databases transaction logs to provide auditing trail.

No, it doesn't. ApexSQL Log has no performance overhead during audit trail capture since it is not involved in it at all. The application simply reads the transaction logs to gather audit data. This allows auditing to be performed during low load times or even be offloaded to another server. Additional login information (NT user name, application name, and client host name), which is not available from the transaction log of SQL7 and SQL2000, is the only information that is actively captured. This capturing process is done by ApexSQL's Connection Monitor which has minimal overhead. SQL2005 and SQL2008 database's transaction log contains NT user's SID information from which full user name can be extracted. In this case, if application name and client host name are not necessary, ApexSQL's Connection Monitor can be stopped and disabled.

No, it cannot. SELECT statements are not logged into SQL Server's transaction log.

ApexSQL Log provides a command line interface with full access to all features available in the graphical user interface. The command Iine interface allows ApexSQL Log to be run unattended. Another advantage is it enables batch processing.

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. If you need to recover the data you can use UNDO and REDO exports.

Unfortunately transaction log doesn't track execution of the procedures, but it reflects data changes made by the procedure call.

This can be implemented for SQL7 and SQL2000 databases, but for SQL2005 and SQL2008 databases ApexSQL Log shows only CREATE/DROP TABLE statements. ApexSQL Log audits INSERT/UPDATE/DELETE operations for user or system tables for all supported SQL Server versions so users can investigate changes to system tables by directly analyzing them. For example, ALTER TABLE operation can be viewed by auditing sysobjects table on SQL7/SQL2000 or sysschobjs table on SQL2005/SQL2008 databases.

Yes, ApexSQL Log fully supports reading and analysis of transaction logs and databases on SQL Server Express.

Yes, it can consume more memory. However, ApexSQL Log adapts its memory growth to available memory so it may not use more memory depending on how much of it is available at the client side.

All versions of ApexSQL Log starting with 2005.10 use data compression for data stored in 'LogData' directory. If you have a version prior to 2005.10 please update it. Furthermore you can optimize this use in the following cases:

1. If you are running analysis only once against any transaction log file or backup then it makes no sense to cache the information for future use. In this case you can use /run_small CLI switch to force the app not to cache the files. Here it is the example:
   
   ApexSQLLog.com /S:(local)\SQL2005 /database:TestDB /sql:result.sql /run_small
2. 'LogData' folder contains temporary information per each analyzed database which speeds up the process, so if you are not going to analyze it anymore you may delete files from it. Anyhow these files will be re-created again by ApexSQL Log on the next analyze.

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

For ApexSQL Log 2005:

1. ApexSqlServerXprocs.dll - the DLL containing all extended procedures used by ApexSQL Log and other ApexSQL products with server-side components.
   
   
2. ApexSqlConnectionMonitor.exe - the executable for Connection Monitor component of ApexSQL Log.
   
   
3. ApexSqlServerHelper.exe - the executable used by ApexSQL Log extended procedures for task done externally to SQL Server process.
   
   
4. ApexSqlServerHelper.sys - the service executable used by ApexSQL Log extended procedures.
   
   
5. ApexSqlLogActivation.exe - the executable used by ApexSQL Log to keep track of activation and evaluation states.
   
   
6. ApexSqlLogApiActivation.exe - the executable used by ApexSQL Log API to keep track of activation and evaluation states.

For ApexSQL Log 2008.01 - 2008.03, the names were changed and ApexSQL Recover activation was added:

1. ApexSqlServerXprocs2008.dll
   
   
2. ApexSqlConnectionMonitor2008.exe
   
   
3. ApexSqlServerHelper2008.exe
   
   
4. ApexSqlServerHelper2008.sys
   
   
5. ApexSqlLogActivation2008.exe
   
   
6. ApexSqlLogApi2008Activation.exe
   
   
7. ApexSqlRecover2008Activation.exe - the executable used by ApexSQL Recover to keep track of activation and evaluation states.

Starting with the ApexSQL Log 2008.05 server-side components were split on ApexSQL Log and ApexSQL Recover:

1. ApexSqlLog2008Activation.exe
   
   
2. ApexSqlLogApi2008Activation.exe
   
   
3. ApexSqlLog2008ConnectionMonitor.exe
   
   
4. ApexSqlLog2008ServerHelper.exe
   
   
5. ApexSqlLog2008ServerHelper.sys
   
   
6. ApexSqlLog2008Xprocs.dll

ApexSQL Recover activation is removed from the ApexSQL Log server-side components and installed as part of ApexSQL Recover server-side components.

At the server-object level, the following are added to the master database:

For ApexSQL Log 2005

xp_ApexSqlConnectionMonitor
xp_ApexSqlConnectionMonitor_Info
xp_ApexSqlConnectionMonitor_Stop
xp_ApexSqlConnectionMonitor_Enable
xp_ApexSqlConnectionMonitor_Disable
xp_ApexSqlLog
sp_ApexSqlConnectionMonitor_Start

For ApexSQL Log 2008.01 - 2008.03

xp_ApexSqlConnectionMonitor2008
xp_ApexSqlConnectionMonitor2008_Info
xp_ApexSqlConnectionMonitor2008_Stop
xp_ApexSqlConnectionMonitor2008_Enable
xp_ApexSqlConnectionMonitor2008_Disable
xp_ApexSqlLog2008
sp_ApexSqlConnectionMonitor2008_Start
xp_ApexSqlLogApi2008
xp_ApexSqlRecover2008

Starting with ApexSQL Log 2008.05 to higher

xp_ApexSqlLog2008ConnectionMonitor
xp_ApexSqlLog2008ConnectionMonitor_Disable
xp_ApexSqlLog2008ConnectionMonitor_Enable
xp_ApexSqlLog2008ConnectionMonitor_Info
xp_ApexSqlLog2008ConnectionMonitor_State
xp_ApexSqlLog2008ConnectionMonitor_Stop
sp_ApexSqlLog2008ConnectionMonitor_Start
xp_ApexSqlLog2008
xp_ApexSqlLogApi2008

New table added to the MSDB database
One table called msdb.APEXSQL_LOG_LOGIN is also added. Please note that you can choose to remove this table after installation IF YOU DON'T NEED IT. This table is used for storing login information as part of ApexSQL Log's Connection Monitor.

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 run setup on server and select server-side components installation.

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. This is done as a safety measure since removing a client doesn't necessarily imply that server-side components need to be uninstalled as well. Furthermore, uninstalling server-side components doesn't remove msdb.dbo.APEXSQL_LOG_LOGIN table since the data contained in that table (captured login information) is user's and not ApexSQL's. Also, if we were to drop the said table each time server-side components are uninstalled, an upgrade to newer version of server-side components would also drop them thus causing loss of captured login information.

The simplest way to verify is to check that the following files have been removed from server's Binn folder.

For ApexSQL Log 2005:

ApexSqlServerXprocs.dll
ApexSqlConnectionMonitor.exe
ApexSqlServerHelper.exe
ApexSqlServerHelper.sys
ApexSqlLogActivation.exe
ApexSqlLogApiActivation

For ApexSQL Log/Recover 2008.01 - 2008.03:

ApexSqlServerXprocs2008.dll
ApexSqlConnectionMonitor2008.exe
ApexSqlServerHelper2008.exe
ApexSqlServerHelper2008.sys
ApexSqlLogActivation2008.exe
ApexSqlLogApi2008Activation.exe
ApexSqlRecover2008Activation.exe

For ApexSQL Log starting with the 2008.05 version

ApexSqlLog2008Activation.exe
ApexSqlLogApi2008Activation.exe
ApexSqlLog2008ConnectionMonitor.exe
ApexSqlLog2008ServerHelper.exe
ApexSqlLog2008ServerHelper.sys
ApexSqlLog2008Xprocs.dll

For ApexSQL Recover starting with the 2008.05 version

ApexSqlRecover2008Activation.exe
ApexSqlRecover2008ServerHelper.exe
ApexSqlRecover2008ServerHelper.sys
ApexSqlRecover2008Xprocs.dll

You may also want to check that the following procedures (extended and stored) have been removed from server's master database.

For ApexSQL Log 2005:

xp_ApexSqlConnectionMonitor
xp_ApexSqlConnectionMonitor_Info
xp_ApexSqlConnectionMonitor_Stop
xp_ApexSqlConnectionMonitor_Enable
xp_ApexSqlConnectionMonitor_Disable
xp_ApexSqlLog
sp_ApexSqlConnectionMonitor_Start

For ApexSQL Log 2008.01 - 2008.03:

xp_ApexSqlConnectionMonitor2008
xp_ApexSqlConnectionMonitor2008_Info
xp_ApexSqlConnectionMonitor2008_Stop
xp_ApexSqlConnectionMonitor2008_Enable
xp_ApexSqlConnectionMonitor2008_Disable
xp_ApexSqlLog2008
sp_ApexSqlConnectionMonitor2008_Start
xp_ApexSqlLogApi2008
xp_ApexSqlRecover2008
 

Starting with ApexSQL Log 2008.05:

xp_ApexSqlLog2008ConnectionMonitor
xp_ApexSqlLog2008ConnectionMonitor_Disable
xp_ApexSqlLog2008ConnectionMonitor_Enable
xp_ApexSqlLog2008ConnectionMonitor_Info
xp_ApexSqlLog2008ConnectionMonitor_State
xp_ApexSqlLog2008ConnectionMonitor_Stop
sp_ApexSqlLog2008ConnectionMonitor_Start
xp_ApexSqlLog2008
xp_ApexSqlLogApi2008

Starting with ApexSQL Recover 2008.05:

xp_ApexSqlRecover2008

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.

ApexSQL Log 2005:

1. Download and execute sql script on your server from Here.
2. On the command line of your server, go to the SQL Server's Binn directory and execute the following command from: ApexSqlServerHelper /unload
3. Delete all binaries that start with ApexSql* from SQL Server's Binn folder.
4. Drop msdb.dbo.APEXSQL_LOG_LOGIN table if you don't need data that's stored in it (it's your login data - that's why we don't drop it automatically)

ApexSQL Log 2008.01 - 2008.03 versions

1. Download and execute sql script on your server from Here.
2. On the command line of your server, go to the SQL Server's Binn directory and execute the following command from: ApexSqlServerHelper2008 /unload
3. Delete all binaries that start with ApexSql* from SQL Server's Binn folder.
4. Drop msdb.dbo.APEXSQL_LOG_LOGIN table if you don't need data that's stored in it (it's your login data - that's why we don't drop it automatically)

Starting with the ApexSQL Log 2008.05 server-side components have been split on ApexSQL Log and ApexSQL Recover and extended stored procedures have been renamed.

1. Download and execute the following script to delete stored procedures and release resources.
2. On the command line of your server, go to the SQL Server's Binn directory and execute the following command from: ApexSqlLog2008ServerHelper /unload
3. Delete all binaries that start with ApexSql* from SQL Server's Binn folder.
4. Drop msdb.dbo.APEXSQL_LOG_LOGIN table if you don't need data that's stored in it (it's your login data - that's why we don't drop it automatically)

If all of the above fail to kill the ApexSQL Connection Monitor process, please try the following:

1. Go to your SQL Server's binn directory (e.g. C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn).
2. Rename ApexSqlConnectionMonitor2008.exe (for ApexSQL Log 2008.01 - 2008.03) and ApexSqlLog2008ConnectionMonitor.exe (for ApexSQL Log 2008.05) to another name.
3. Go to Task Manager and kill the ApexSqlConnectionMonitor2008.exe process.
4. Delete the (renamed) file.

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 log full row information so ApexSQL Log has to use multiple complementary tactics to figure out row's state. For example, one of these tactics is to go back through loaded transaction logs in search of update row's INSERT operation. Once the corresponding INSERT is found, the application can then 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) and other tactics fail as well (through reasons of their own.) When all these complementary tactics fail, the only thing left for ApexSQL Log to show is the binary data that was written by SQL Server to the transaction log - which is what you are seeing.

There are several ways to remedy this though at the end it all comes down to the availability of database and transaction log data. Here are some of the most common situations:

1. If you are using transaction log backups on an online database make sure that you include the entire transaction log chain (all the backups) up to the current database state plus online transaction log file
2. If you are using transaction log backups on an older version of the database make sure that you include the entire transaction log chain (all the backups) that have been done since that older version was branched. Make sure that the older version of the database is read-only and is not being changed on its own as that may invalidate some results.
3. If you have a full database backup with the entire transaction log backup chain after it, specify the full db backup as additional database source *and* as a transaction log backup (since it contains the part that was active during the backup.) If you have differential backups as well add them as additional data sources (the application will figure out which has the most relevant data.)
4. If you are using a detached LDF file and you have a database backup (full or differential) that has been done during its "life" then specify it as additional source of data.

In general try to give the application as much database and transaction log data as you have available and that's relevant to the time frame you are interested in. The application handles duplicate information and knows which one is the most relevant to its task so there is no chance of doing harm by specifying too much data.

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.

In general, when you see less operations than expected, these are things to consider:

1. You could be using an online transaction log of a restored database. It's a common misconception to presume that full database backup includes full transaction log information. In fact full database backup includes only the active part of the transaction log at the time of the backup plus all the transactions that happened during the backup itself.
2. Transaction log may not contain any transactions affecting state of the user or system table data. ApexSQL Log doesn't show all information it sees in the transaction log but only a subset directly affecting state of user or system tables. Operations affecting, for example, indexes are never shown.
3. Transaction log may not contain any operations that match the filter defined by the user. If the default filter was used, by default ApexSQL Log filters out all operations that affected system tables. If a customer filter was used then it's recommended to clear it and rerun the process as it may show more operations.

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 restored 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 and database backups as additional sources of database data which improves the accuracy of auditing (namely some UPDATE reconstruction tactics use this information to greatly improve accuracy and lower the frequency of failed UPDATE reconstructions.) Furthermore, if the transaction log info is spread across multiple files of these types, they can be selected as a group and the whole set of data across all the files is then made available to ApexSQL Log.

No, that'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 ApexSQL Log's server-side components. Please take into account that whenever the application has to analyze remote files (transaction log, database backup) the network becomes the bottleneck of the process. So if you have an option always prefer to run the ApexSQL Log client on the machine that stores the transaction log sources being analyzed (if online transaction log files then directly on server, if detached transaction log files or transaction log backups then on the machine on which they are stored.)

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 by SQL Server. 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 but 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 showing only transactions in a certain time range or for a certain user, yes. Again, ApexSQL Log has many great filtering options (including field filtering with comparison operators) that allow easy search for specific transactions.

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.

There is no limit on the number of rows shown. ApexSQL Log shows only those operations that affected table data (be it user or system) so logged operations on say indexes will never be shown. Furthermore, by default, all changes to system tables are not shown but that can be changed in the filter configuration. In any case if you are seeing less data than what you were expecting please try changing the filter and/or adding more transaction log sources.

Yes this task can be implemented with help of command line interface 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 (BULK/XML/CSV/SQL Script and even UNDO/REDO scripts.) Through command line one could also filter tables, users, and time ranges and so on.

ApexSQL Log only reads the information that is logged by SQL Server. A web application that uses only one account to connect to SQL Server will always have just one account logged to transaction log. 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.

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.

Unfortunately ApexSQL Log is unable to audit changes in BLOB data types. ApexSQL Log outputs BLOB data types as a varbinary since all text/ntext/image fields are stored as 16 byte pointers in the row itself. ApexSQL Log reads the pointer (as a binary) that you have seen but doesn't read the values that the pointer points to.

Deleted/dropped BLOB can sometimes be recovered though by ApexSQL Recover product.

TRN is one of the extensions used for transaction log backups while LDF is the default extension for actual transaction log files. In any case ApexSQL Log can read online and detached transaction log files as well as transaction log backups. You will find these options in "Log Selection Wizard".

Yes, you can audit such large databases. It would be better to do it through command line interface as it's less memory consuming.

Whenever the application has to analyze remote files (transaction log, database backup) the network becomes the bottleneck. To speed up the analyze process please, make a backup of the transaction log you are going to analyze, copy it locally and analyze this backup.

You seem to have ApexSQL Log 2005 or prior. Unfortunately this application version doesn't support Chinese characters (or other non-UNICODE collations) and it's a known limitation. Starting from ApexSQL Log 2008 we fully support non-UNICODE collations, so you may want to look into upgrading your version.

Unfortunately no, seeing the DELETE/INSERT pair is completely normal – this is what SQL Server actually does when you change a value that's part of a clustered index. The reason for this is that each change to cluster index really implies changing the position of the row in the physical layout of the table itself which in turn implies that it has to be deleted from its old position (page/slot) and then inserted into the new position (page/slot). Hence the DELETE/INSERT pair is logged instead of a simple UPDATE.

Yes, full database backup truncates the log.

Yes, UNDO script for operations within one transaction can be easily created with help of the following steps:

1. Invoke Main Grid's context menu and select 'Select All Operations Within This Transaction'.
2. Select 'Check Selected Operations' item from the same context menu.
3. All selected items are checked and then just select 'Tools | Create Undo Script' menu item to generate final script.

Unfortunately that's really difficult to determine, because this value is differ on each server. The reason is not only the differences in server hardware (these processes are both I/O and CPU intensive) but much more importantly differences in database usage patterns. For example, on a pattern with large number of INSERT operations and small number of UPDATE statements the application will perform much better than on a pattern of lots of UPDATE statements. Another example is the difference between 1 Gb of transaction log data logged due to 1,000,000 1 kb rows vs. 1 row with 1 Gb worth of BLOB data.

These are the most effective ways to increase the performance of transaction log analysis in the order of their effectiveness:

1. Make sure that client is running on the same computer where transaction log files are located. While ApexSQL Log client can access online transaction log files through SQL Server connection it is much faster to run the client on the server itself whenever online transaction log files are analysed. If analysis is done on transaction log files or detached log files then it's best to run the client on the system that stores them, even if they are accessible through a network share. Whenever the application has to analyze remote files the network becomes the bottleneck.
2. Define Time Range: this filter has the biggest effect on the application performance; when defined the analysis is performed within specified time range only, transactions from the other time interval are ignored.
3. Specify Tables: specifying required for analysis tables with unchecked Show operations on dropped tables option will not load Main Grid with operations from tables that no longer exist and thus will decrease memory consumption on the client and increase overall application performance.
4. Use fine-grained filters: whenever it's possible use the tightest filters that will return the results you need. This won't actually increase the performance of the application but will increase the quality of results you get from the application and thus avoid wasting your time on irrelevant results.
5. Turn Check to Reconstruct UPDATES automatically option OFF: when this option is unchecked ApexSQL Log does not reconstruct updates during log analysis and thus significantly increases performance. This option doesn't affect the speed of Main Grid population with analyzed results, but instead speeds up results export if such is performed.

Create the UNDO script for data loss due to DELETE operations. But if everything is lost and need to be recovered then generate the REDO script for the selected transactions.

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 on all operating systems from Windows 2000 onwards.

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.

It depends on whether ApexSQL Log client is running on the active node or it is running outside of the cluster. In the first case you will have to have the same configuration (e.g. batch files for command line interface) on on each node in the cluster. When the failover happens, log auditing on the new active node will need to be started manually. In the second case, when the application is running outside of the cluster, it will be automatically redirected by the cluster itself to the active node (same as all other applications running outside of the cluster.) In both cases you will need to install and activate server-side components on each node in the cluster.

You can turn off the Connection Monitor and delete all previously recorded logins by executing the following script for ApexSQL Log 2005 server-side components:

-- 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

and for ApexSQL Log 2008 server-side components:

-- Turns off the Connection Monitor.
USE MASTER

EXEC XP_APEXSQLCONNECTIONMONITOR2008_STOP

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

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

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

TRUNCATE TABLE APEXSQL_LOG_LOGIN

In general, this will not affect ApexSQL Log's basic ability to read and process transaction logs. The Connection Monitor is a value-added feature that captures information about individual logins that SQL Server itself does not log into transaction log. By turning this off, you will lose specifically all information on all NT logins (in SQL7/SQL2000 only) and all information on the connecting client application and client host info (in all versions of SQL Server.) All other information comes directly from transaction log and is not affected by disabling Connection Monitor.

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 say 30 days). We would recommend to incorporate archiving of the connection data along with the transaction log backup archival.

State of ApexSQL Log CLI can be checked from Task Manager:

1. Open Task Manager
2. Select Processes tab and open Select Columns dialog (View | Select Columns...)
3. Select I/O Read Bytes
4. Save changes and check this value for ApexSQLLog.exe value.

If the value keeps changing then that means that ApexSQL Log is still analyzing transaction log sources.

Filters that are specified in the Log Selection Wizard are also available through the command line interface. They can be applied to get narrower results for UNDO/REDO scripts. For example, to generate UNDO script for all UPDATE operations that happened on 7/7/2008 the following command could be used:

ApexSQLLog.com /server:<Server name> /database:<database name> /undo:<Path to resulting UNDO script file> /tables:<specify table for audit, several tables are separated by space> /exclude_system_tables /operations:UPD /from:"2008-07-07 00:00:00" /to:"2008-07-08 00:00:00" /verbose

Similar to UNDO scripts, you can also create REDO script. In this example we are generating a REDO script for all INSERT operations that happened on 7/7/2008.

ApexSQLLog.com /server:<Server name> /database:<database name> /redo:<Path to resulting REDO file> /tables:<specify table for audit, several tables are separated by space> /exclude_system_tables /operations:INS /from:"2008-07-07 00:00:00" /to:"2008-07-08 00:00:00" /verbose

You can of course generate an UNDO or REDO script for all operations by simply omitting /operations switch. Common for all filtering switches is that when they are omitted, they are considered to be turned off.