FAQs - ApexSQL Log
Important Notice
ApexSQL Log doesn't support auditing or recovery of out-of-row BLOB values, encrypted data, data compressed on row or page level, sparse columns, CLR types, FILESTREAM BLOBs among other specific features of SQL Server. We are constantly improving our software and we strive toward full support of all SQL Server features so if you are unsure whether a specific feature of SQL Server is supported please contact our support through our forum or by writing to support@apexsql.com. Please click Here to review ApexSQL’s policy on refunds.
Licensing and Evaluation
|
What is the license model for Apex SQL 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.
|
|
Are there any restrictions or limitations in the evaluation version
of ApexSQL Log?
|
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:
|
|
What is the license model for SQL server clusters?
|
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.
Activation
|
|
How do I activate the software?
|
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.
General
|
|
What SQL Server versions are supported by ApexSQL Log?
|
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.
|
|
Does ApexSQL Log use triggers?
|
ApexSQL Log does not use triggers; instead, it uses databases transaction logs to provide auditing trail.
|
|
Does ApexSQL Log have a large performance overhead?
|
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.
|
|
Can ApexSQL Log audit SELECT statements?
|
No, it cannot. SELECT statements are not logged into SQL Server's transaction log.
|
|
Does ApexSQL Log have a command line interface? If it does, can I run it unattended? |
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.
|
|
Can I export log audit results to a file?
|
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.
|
|
Does the transaction log registers stored procedure execution?
|
Unfortunately transaction log doesn't track execution of the procedures, but it reflects data changes made by the procedure call.
|
|
Can changes to database schema or permissions be viewed or rolled back?
|
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.
|
|
Does ApexSQL Log support reading of transactions logs on SQL Server Express?
|
Yes, ApexSQL Log fully supports reading and analysis of transaction logs and databases on SQL Server Express.
|
|
Does ApexSQL Log consume more memory if the MSDB grows?
|
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.
|
|
The size of the 'LogData' folder is too big. How can it be reduced?
|
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:
- 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
- '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.
Server-side Components
|
|
What exactly does ApexSQL Log install on my server?
|
ApexSQL Log's installation requirements are fairly small, and their overall effect on performance is negligible. At the file level, ApexSQL Log installs six components, all in the SQL Server instance’s ‘Binn’ folder. These are for ApexSQL Log 2005:
-
ApexSqlServerXprocs.dll - the DLL
containing all extended procedures used by ApexSQL Log and other ApexSQL
products with server-side components.
-
ApexSqlConnectionMonitor.exe - the
executable for Connection Monitor component of ApexSQL Log.
-
ApexSqlServerHelper.exe - the executable
used by ApexSQL Log extended procedures for task done externally to SQL
Server process.
-
ApexSqlServerHelper.sys - the service
executable used by ApexSQL Log extended procedures.
-
ApexSqlLogActivation.exe - the executable
used by ApexSQL Log to keep track of activation and evaluation states.
-
ApexSqlLogApiActivation.exe - the
executable used by ApexSQL Log API to keep track of activation and
evaluation states.
At the server-object level, there are a total of seven procedures and one table. There are four extended procs included in the dll file above - xp_ApexSqlConnectionMonitor, xp_ApexSqlConnectionMonitor_Info, xp_ApexSqlConnectionMonitor_Stop, xp_ApexSqlConnectionMonitor_Enable, xp_ApexSqlConnectionMonitor_Disable, xp_ApexSqlLog. There is also one stored procedure - master..sp_ApexSqlConnectionMonitor_Start - and one table (used for storing login information as part of ApexSQL Log’s Connection Monitor) - msdb..APEXSQL_LOG_LOGIN.
For ApexSQL Log 2008 components name were changed on and ApexSQL Recover activation was added:
-
ApexSqlServerXprocs2008.dll
-
ApexSqlConnectionMonitor2008.exe
-
ApexSqlServerHelper2008.exe
-
ApexSqlServerHelper2008.sys
-
ApexSqlLogActivation2008.exe
-
ApexSqlLogApi2008Activation.exe
-
ApexSqlRecover2008Activation.exe - the
executable used by ApexSQL Recover to keep track of activation and
evaluation states.
Names of extended stored procedures were changed accordingly on:
-
xp_ApexSqlLog2008
-
xp_ApexSqlLogApi2008
-
xp_ApexSqlRecover2008
-
xp_ApexSqlConnectionMonitor2008
-
xp_ApexSqlConnectionMonitor2008_Stop
-
xp_ApexSqlConnectionMonitor2008_Info
-
xp_ApexSqlConnectionMonitor2008_Enable
-
xp_ApexSqlConnectionMonitor2008_Disable
-
sp_ApexSqlConnectionMonitor2008_Start
|
|
Remote installation of server components failed. What can I do to make it work? |
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.
|
|
How do I uninstall the server-side components?
|
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.
|
|
Does uninstalling ApexSQL Log client remove any server-side components?
|
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.
|
|
How can I verify that server-side components have been removed?
|
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
and for ApexSQL Log/Recover 2008:
ApexSqlServerXprocs2008.dll
ApexSqlConnectionMonitor2008.exe
ApexSqlServerHelper2008.exe
ApexSqlServerHelper2008.sys
ApexSqlLogActivation2008.exe
ApexSqlLogApi2008Activation.exe
ApexSqlRecover2008Activation.exe
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_ApexSqlLog
xp_ApexSqlLogApi
xp_ApexSqlConnectionMonitor
xp_ApexSqlConnectionMonitor_Info
xp_ApexSqlConnectionMonitor_Stop
xp_ApexSqlConnectionMonitor_Enable
xp_ApexSqlConnectionMonitor_Disable
sp_ApexSqlConnectionMonitor_Start
and for ApexSQL Log/Recover 2008:
xp_ApexSqlLog2008
xp_ApexSqlLogApi2008
xp_ApexSqlRecover2008
xp_ApexSqlConnectionMonitor2008
xp_ApexSqlConnectionMonitor2008_Stop
xp_ApexSqlConnectionMonitor2008_Info
xp_ApexSqlConnectionMonitor2008_Enable
xp_ApexSqlConnectionMonitor2008_Disable
sp_ApexSqlConnectionMonitor2008_Start
If all these elements have been removed - server components have been removed.
|
|
How can I remove these components manually?
|
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 you are trying to uninstall ApexSQL Log 2005:
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_InitLogNav')
EXEC MASTER.DBO.SP_DROPEXTENDEDPROC
'xp_InitLogNav'
IF EXISTS (SELECT *
FROM MASTER.DBO.SYSOBJECTS
WHERE NAME = 'xp_UnInitLogNav')
EXEC MASTER.DBO.SP_DROPEXTENDEDPROC
'xp_UnInitLogNav'
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_ApexSQLConnectionMonitor_Enable')
EXEC MASTER.DBO.SP_DROPEXTENDEDPROC
'xp_ApexSQLConnectionMonitor_Enable'
IF EXISTS (SELECT *
FROM MASTER.DBO.SYSOBJECTS
WHERE NAME = 'xp_ApexSQLConnectionMonitor_Disable')
EXEC MASTER.DBO.SP_DROPEXTENDEDPROC
'xp_ApexSQLConnectionMonitor_Disable'
IF EXISTS (SELECT *
FROM MASTER.DBO.SYSOBJECTS
WHERE NAME = 'xp_EnableLiveLog')
EXEC MASTER.DBO.SP_DROPEXTENDEDPROC
'xp_EnableLiveLog'
IF EXISTS (SELECT *
FROM MASTER.DBO.SYSOBJECTS
WHERE NAME = 'xp_DisableLiveLog')
EXEC MASTER.DBO.SP_DROPEXTENDEDPROC
'xp_DisableLiveLog'
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 = 'ln_open')
EXEC MASTER.DBO.SP_DROPEXTENDEDPROC
'ln_open'
IF EXISTS (SELECT *
FROM MASTER.DBO.SYSOBJECTS
WHERE NAME = 'ln_is_open')
EXEC MASTER.DBO.SP_DROPEXTENDEDPROC
'ln_is_open'
IF EXISTS (SELECT *
FROM MASTER.DBO.SYSOBJECTS
WHERE NAME = 'ln_is_valid')
EXEC MASTER.DBO.SP_DROPEXTENDEDPROC
'ln_is_valid'
IF EXISTS (SELECT *
FROM MASTER.DBO.SYSOBJECTS
WHERE NAME = 'ln_seekga')
EXEC MASTER.DBO.SP_DROPEXTENDEDPROC
'ln_seekga'
IF EXISTS (SELECT *
FROM MASTER.DBO.SYSOBJECTS
WHERE NAME = 'ln_seekgr')
EXEC MASTER.DBO.SP_DROPEXTENDEDPROC
'ln_seekgr'
IF EXISTS (SELECT *
FROM MASTER.DBO.SYSOBJECTS
WHERE NAME = 'ln_tellg')
EXEC MASTER.DBO.SP_DROPEXTENDEDPROC
'ln_tellg'
IF EXISTS (SELECT *
FROM MASTER.DBO.SYSOBJECTS
WHERE NAME = 'ln_read')
EXEC MASTER.DBO.SP_DROPEXTENDEDPROC
'ln_read'
IF EXISTS (SELECT *
FROM MASTER.DBO.SYSOBJECTS
WHERE NAME = 'ln_close')
EXEC MASTER.DBO.SP_DROPEXTENDEDPROC
'ln_close'
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 LOGNAVXP
DBCC APEXSQLLOGXPROCS
DBCC APEXSQLSERVERXPROCS
The result of this script should be something like this:
Stopping ApexSQL Log Monitor...
ApexSQL Log 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:
- ApexSqlConnectionMonitor.exe
- ApexSqlLogActivation.exe
- ApexSqlLogApiActivation.exe
- ApexSqlServerHelper.exe
- ApexSqlServerHelper.sys
- ApexSqlServerXprocs.dll
If this step finishes successfully as well - there is nothing left of ApexSQL Log's server side components on your server.
For ApexSQL Log 2008 please execute the following script:
IF EXISTS (SELECT *
FROM MASTER.DBO.SYSOBJECTS
WHERE NAME = 'xp_ApexSqlConnectionMonitor2008_Stop')
EXEC MASTER.DBO.XP_APEXSQLCONNECTIONMONITOR2008_STOP
IF EXISTS (SELECT *
FROM MASTER.DBO.SYSOBJECTS
WHERE NAME = 'xp_ApexSqlLog2008')
EXEC MASTER.DBO.SP_DROPEXTENDEDPROC
'xp_ApexSqlLog2008'
IF EXISTS (SELECT *
FROM MASTER.DBO.SYSOBJECTS
WHERE NAME = 'xp_ApexSqlLogApi2008')
EXEC MASTER.DBO.SP_DROPEXTENDEDPROC
'xp_ApexSqlLogApi2008'
IF EXISTS (SELECT *
FROM MASTER.DBO.SYSOBJECTS
WHERE NAME = 'xp_ApexSqlRecover2008')
EXEC MASTER.DBO.SP_DROPEXTENDEDPROC
'xp_ApexSqlRecover2008'
IF EXISTS (SELECT *
FROM MASTER.DBO.SYSOBJECTS
WHERE NAME = 'xp_ApexSqlConnectionMonitor2008')
EXEC MASTER.DBO.SP_DROPEXTENDEDPROC
'xp_ApexSqlConnectionMonitor2008'
IF EXISTS (SELECT *
FROM MASTER.DBO.SYSOBJECTS
WHERE NAME = 'xp_ApexSqlConnectionMonitor2008_Stop')
EXEC MASTER.DBO.SP_DROPEXTENDEDPROC
'xp_ApexSqlConnectionMonitor2008_Stop'
IF EXISTS (SELECT *
FROM MASTER.DBO.SYSOBJECTS
WHERE NAME = 'xp_ApexSqlConnectionMonitor2008_Info')
EXEC MASTER.DBO.SP_DROPEXTENDEDPROC
'xp_ApexSqlConnectionMonitor2008_Info'
IF EXISTS (SELECT *
FROM MASTER.DBO.SYSOBJECTS
WHERE NAME = 'xp_ApexSqlConnectionMonitor2008_Enable')
EXEC MASTER.DBO.SP_DROPEXTENDEDPROC
'xp_ApexSqlConnectionMonitor2008_Enable'
IF EXISTS (SELECT *
FROM MASTER.DBO.SYSOBJECTS
WHERE NAME = 'xp_ApexSqlConnectionMonitor2008_Disable')
EXEC MASTER.DBO.SP_DROPEXTENDEDPROC
'xp_ApexSqlConnectionMonitor2008_Disable'
IF EXISTS (SELECT *
FROM MASTER.DBO.SYSOBJECTS
WHERE NAME = 'sp_ApexSqlConnectionMonitor2008_Start')
EXEC MASTER.DBO.SP_PROCOPTION
'sp_ApexSqlConnectionMonitor2008_Start' ,
'startup' ,
'false'
IF EXISTS (SELECT *
FROM MASTER.DBO.SYSOBJECTS
WHERE NAME = 'sp_ApexSqlConnectionMonitor2008_Start')
EXEC MASTER.DBO.SP_EXECUTESQL
N'DROP PROCEDURE sp_ApexSqlConnectionMonitor2008_Start'
DBCC APEXSQLSERVERXPROCS2008
and then remove the following ApexSQL Log components from server's Binn directory:
- ApexSqlConnectionMonitor2008.exe
- ApexSqlLogActivation2008.exe
- ApexSqlLogApiActivation2008.exe
- ApexSqlRecoverActivation2008.exe
- ApexSqlServerHelper2008.exe
- ApexSqlServerHelper2008.sys
- ApexSqlServerXprocs2008.dll
Transaction Log Auditing
|
|
Sometimes some UPDATE operations are not shown with any data or are shown with raw binary data. When these operations are exported a message similar to "RECORD CHANGE HISTORY COULD NOT BE RECONSTRUCTED FOR UPDATE " is shown in the resulting export file. Is this normal? Why does this happen?
|
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:
- 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
- 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.
- 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.)
- 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.
|
|
After I open a log I am seeing only a subset of all rows from the transaction log - why am I not seeing all of the transaction log information?
|
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:
- 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.
- 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.
- 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.
|
|
I have switched my database to FULL RECOVERY model but ApexSQL Log is still not showing any transactions?
|
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.
|
|
I have restored a full database backup and can see the restored transaction log file has the original size. However, when I use ApexSQL Log with databases online transaction logs, it doesn't display any transactions made prior to the backup. Why is that?
|
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.
|
|
I understand ApexSQL Log can capture structural changes to my database from the log. How do I view this?
|
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.
|
|
Does ApexSQL Log work with both data backup files and transaction log files?
|
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.
|
|
Do I have to map the log directory of the server as a network drive on client machine before I can access it?
|
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.)
|
|
Does ApexSQL Log permit the user to only choose those table fields he or she is interested in tracking?
|
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.
|
|
What's the advantage of the history tab view when viewing an inserted row?
|
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.
|
|
Why are log records displayed even after truncating the log?
|
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.)
|
|
Can the product be positioned to a point in time using a timestamp?
|
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.
|
|
Can the product be explicitly positioned in a log?
|
'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.
|
|
Is it possible to have fields that are of a text or ntext data type to display the actual text that was entered and not a 32 digit hexadecimal number?
|
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.
|
|
ApexSQL Log will not display more than a certain low number of records from our logs. Is this a limit in the evaluation version?
|
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.
|
|
Is it possible to run ApexSQL Log as a background job? Each night, we will be creating logs for 6 databases to evaluate. We would like to be able to schedule ApexSQL Log to run against these backups overnight to provide us reports in the morning to review.
|
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.
|
|
Will ApexSQL Log be able to identify a specific user id if passed from a web application?
|
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.
|
|
When is table marked as unknown?
|
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.
|
|
What are splitnext, split transactions?
|
'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.
|
|
Can database operations extracted from the on-line or archive logs be associated with the proper transactions?
|
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.
|
|
Can the product read both archive and online transaction logs while the database is active?
|
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).
|
|
From the data presented, is the table name, operation type and column values available for each operation?
|
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:
- Corresponding table's meta-data is available and matches the meta-data as it was at the moment of operation.
- 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.
- The column is not of BLOB type.
|
|
Can ApexSQL Log audit BLOB data 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.
|
|
Does ApexSQL Log reads SQL Server's transaction logs (TRN)? Or can it only read the LDF log files?
|
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".
|
|
Can ApexSQL Log work with databases up to 1Tb in space?
|
Yes, you can audit such large databases. It would be better to do it through command line interface as it's less memory consuming.
|
|
The process of transaction log analyze over the network takes a lot of time. How it can be speed up?
|
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.
|
|
Chinese characters from database with Chinese collation are corrupted in main grid and REDO/UNDO scripts. How can it be solved?
|
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.
|
|
ApexSQL Log is showing UPDATE records as DELETE/INSERT pairs for clustered index value. Can this information be shown as UPDATE record?
|
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.
|
|
Does the transaction log get truncated after a full database backup?
|
Yes, full database backup truncates the log.
|
|
Can I generate UNDO script for operations within one transaction?
|
Yes, UNDO script for operations within one transaction can be easily created with help of the following steps:
- Invoke Main Grid's context menu and select 'Select All Operations Within This Transaction'.
- Select 'Check Selected Operations' item from the same context menu.
- All selected items are checked and then just select 'Tools | Create Undo Script' menu item to generate final script.
|
|
What is the estimation on the time it would take to process transaction log files with size more than 50Gb?
|
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.
|
|
How do I get my transaction log detail data faster?
|
These are the most effective ways to increase the performance of transaction log analysis in the order of their effectiveness:
- 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.
- 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.
- 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.
- 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.
- 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.
|
|
What should I use UNDO or REDO during the recovery process?
|
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.
Exporting Audited Data
|
|
How do I export the log data and analyze it with Excel?
|
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.
|
|
How to export just some transactions?
|
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.
Software Configuration
|
|
Does the product have a large footprint or adversely impact database performance?
|
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.
|
|
Does the product read continuously or is it more of a batch tool?
|
ApexSQL Log is a completely passive batch tool except for Connection Monitor, which constantly monitors new SQL Server connections.
|
|
Are there any issues with reading the active logs? Why is this a recent feature? Is this only supported on specific versions of Windows?
|
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.
|
|
Are there any onerous operational requirements for reading the logs, for example, special OS level tricks/security issues that we need to worry about?
|
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.
|
|
What is the impact of multiple ApexSQL Log clients hitting the transaction log?
|
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.
|
|
We recently changed server machine name and now ApexSQL Log client can't connect to it and Connection Monitor on it won't start. Why is that and what can I do to fix it?
|
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:
- Execute on master database these two queries:
- EXEC sp_dropserver <old_name>
- EXEC sp_addserver <new_name>, 'local'
- Restart SQL Server service.
|
|
How does ApexSQL Log handle failover in a clustered environment?
|
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.
Connection Monitor
|
|
I wish to disable ApexSQL Log's Connection Monitor. How can I achieve that and will it affect ApexSQL Log's log-reading functionality as well? |
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.
|
|
How is the Connection Monitor data table managed for size and historical reference?
|
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.
Command-line Interface
|
|
How to determine if ApexSQL Log CLI has stopped working? |
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.
|
|
How can I generate Rollback (UNDO) or Reconstruct (REDO) scripts
from the CLI?
|
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.
PRODUCT PAGE
Why ApexSQL?
To See the Top Reasons to Buy Apex SQL Software tools: Click
Here.
$1,999
All ApexSQL Tools with 1 yr Maint and all New tools Free for 1 yr.
Learn More
FREE
ApexSQL Tools and Add-ins. Learn More
Unbeatable Killer Upgrades and Discounts.
Learn More
Click Here for our ApexSQL Gear Store.
Show All
Hide All)