Switch to standard view 
  Sybase logo

August 2010 Edition

How to Handle Assertion Failed Messages in SQL Anywhere

SQL Anywhere has many internal checks that have been designed to detect any possible database corruption as soon as possible. When an assertion failure occurs, the database server immediately cancels all current requests and an error is reported for all subsequent requests until the database server shuts down. By refusing to continue database operations after an assertion failure, the database server minimizes the chances for corruption to spread in the database.

When an assertion occurs, the database server freezes and the database server messages window or output log reports the following message:

*** ERROR *** Assertion failed: 123456 (x.0.x.xxxx)
Error message follows with information about the cause of the assertion.

An assertion number and a description of the error that caused the assertion follow the assertion failure. An assertion failure indicates that the database server encountered an unexpected condition. To protect the database from any further damage when an unexpected condition is encountered, the database server terminates all current connections, refuses any new connections, and then stops the database.

An unexpected condition could result in either a hard or soft assertion failure, and may be the result of a software bug. A hard assertion indicates that the integrity of the physical database file or transaction log has been compromised, usually by an external process or hardware failure. Soft assertions can occur when the database server encounters a situation that can lead to incorrect query results, improper functioning of background processes, or other unanticipated faults that have the potential to affect multiple connections. The root cause of any database assertion failure can potentially be a problem with the physical database file.

What to do when you receive an assertion failed message


  1. Shut down the database server, if it is still running. This action protects the other databases running on the database server from potential corruption.
  2. Make a backup copy of the database file and transaction log. You should also make a backup copy of any old transaction logs that have yet to be deleted. The backup copies of these files should be completed with a straight file-to-file copy (do not use dbbackup or another backup procedure).
  3. Attempt to restart the database server and load the database file.
  4. If the database starts successfully, validate the database using the Validation utility (dbvalid or alternatively, you can use the sa_validate procedure).
  5. If the database starts successfully, validate the database using the Validation utility (dbvalid or alternatively, you can use the sa_validate procedure).
  • If dbvalid reports errors or another assertion failure occurs, then it is possible that the database file is corrupt. In this scenario, the best course of action is to resort to your tested backup and recovery strategy. If you do not have a backup and recovery strategy, contact Technical Support by calling 1-800-8Sybase (1-800-879-2273) or visiting http://www.sybase.com/support .
  • If dbvalid completes successfully and reports no errors, then the database can be put back into production. If subsequent assertion failures occur, revert to your tested backup and recovery strategy. If you are still experiencing problems after reverting to your tested backup and recovery strategy, contact Technical Support.

    Even if dbvalid does not return errors after validating a database, there is still potential for subtle corruption. The only way to remove pre-existing corruption in the database files is to rebuild the database using the unload/reload process. Restarting the database alone never ensures that the database file is free from corruption.
    For more information about the Validation utility and its syntax, see http://dcx.sybase.com/index.html#1200en/dbadmin_en12/dbvalid.html .
    For more information about the sa_validate system procedure, see http://dcx.sybase.com/index.html#1200en/dbreference_en12/sa-validate-sysproc.html.

  1. If the database server starts correctly, the database attempts to go through a recovery process. If the recovery process is successful and the database must be put into production before validating it and before you can finish repairing any problems, then the database should be restarted with a new transaction log. To have the database start with a new transaction log, shut down the already running database, rename the old transaction log, and then restart the database.

If the database does not start correctly in step 5, then forcing the database to start without the transaction log is an option only if the database is not involved in replication or synchronization (MobiLink, SQL Remote, or Replication Server). This can be done using the following command:

dbengX -f dbfile.db

  • X is 50, 6, 7, 8, 9, 10 , 11, or 12
  • Replace dbfile.db with the path to the location of the database file

Normally, when the database server goes through recovery it rolls the database file back to the previous checkpoint and then applies all transactions in the transaction log after that checkpoint. Using dbengX -f allows recovery to the last checkpoint without applying the transactions in the transaction log. This is only true if the transaction log is not present in the location where the database file expects the transaction log to be located. If the transaction log is present, then the database server attempts to apply the transactions in the log since the last checkpoint, regardless of whether -f was used on the database server command line. If you suspect that the transaction log is corrupt, then the transaction log must be renamed before using dbengX -f to ensure that the original transaction log does not get used.

How can I protect my database against an assertion failure?

The best protection against an assertion failure is a tested backup and recovery strategy that accounts for operating system crashes, disk failure, file corruption, and total machine failure. A tested backup and recovery strategy allows for minimum downtime in the event of an assertion failure.
For more information about backup and recovery, see http://dcx.sybase.com/index.html#1200en/dbadmin_en12/da-new-backup.html.

Recovering from a valid backup of the database and transaction logs

Recovering the database from a valid backup of the database file and transaction log is the ideal method for recovering from a database assertion or corruption. If there are no transaction logs missing in the sequence from the last valid backup to the time of the assertion failure, then there will be no data loss as a result of the assertion.
The following steps should have already been taken as part of your backup strategy. Ideally, a copy of the database backup should have already been validated to verify the integrity of the backup.
Note: Always use a copy of the backup or run the database server in read-only mode to test the validity of the database.
To ensure the backup database is not corrupt, run dbunload and create a new database from the backup copy. If the database rebuilds without any errors on the reload, then you know the backup copy is valid.
For more information about rebuilding a version 10.0.0 or earlier database, see http://dcx.sybase.com/index.html#1200en/sachanges/unloading-reloading-upgrading-newjasper.html.
For more information about rebuilding a version 10.0.1 or later database, see http://dcx.sybase.com/index.html#1200en/sachanges/v10upgrade-s-4897531.html.
Now that the backup of the database has been validated, restore a copy of the backup. You apply the transaction logs to this restored copy of the database. Applying the transaction logs executes all transactions run against the database up to the time of the assertion failure. If the transaction log has not been truncated since the last backup, you only need to apply the single transaction log. This can be done as follows:

dbengX dbfile.db -a dbfile.log

  • X is 50, 6, 7, 8, 9, 10, 11, or 12
  • Replace dbfile.db with the path to the database file
  • Replace dbfile.log with the path to the database transaction log

If the transaction logs have been renamed and truncated, then use this command to apply each of the transaction logs in sequence. The first transaction log to be applied is the transaction log that was backed up with the database file. To avoid any naming conflicts, all the transaction logs to be applied should be stored in a separate directory from the database file. When all the transaction logs have been applied, restart the database. A new transaction log is created for the database, which becomes the current transaction log.

For version 10 and later, you can use the –ad option instead of the –a option. This option specifies the directory containing the transaction log files to be applied to the database. It also determines the order of the transaction logs to be applied.

dbengX dbfile.db -ad c:\backup

This process will not break synchronization or replication, but all of the applied transaction logs must be kept in case there is an offset in one of these transaction logs that is required for synchronization or replication. If this is not a synchronization or replication environment, the applied transaction logs do not need to be retained.

Recovering a database if there are no valid backups, but a valid transaction log exists that has been used since the database was created

SQL Anywhere database architecture uses a transaction log and database file. The transaction log records all statements executed against the database. As a result, a single, all-inclusive transaction log can be used to recreate an entire database. This process will break synchronization and replication.

If a LOAD TABLE operation was run and logged in the original transaction log file, the database server attempts to re-execute it. For these commands to succeed, the data files must still be present in the same location as they were originally and they must contain the same data.

To recover the database with this process

  1. Use the Initialization utility to create a new database with the same initialization parameters as the existing database. You can obtain most initialization parameters using the Information utility (dbinfo). Note that in some cases you may not be able to run dbinfo on a corrupt database.

Note: The Information utility only returns information about Java in the database for SQL Anywhere 8 or 9. If you suspect you are using Java in the database with SQL Anywhere 7 or earlier, use the process outlined in "Recovery from a Single, All-Inclusive Log File".
For more information about the Initialization utility, see http://dcx.sybase.com/index.html#1200en/dbadmin_en12/dbinit.html.
For more information about the Information utility, see http://dcx.sybase.com/index.html#1200en/dbadmin_en12/dbinfo.html.

  1. Make a backup copy of the transaction log in case any problems occur while attempting to translate the transaction log.
  2. Translate the transaction log using the Log Translation utility.

This step creates a SQL script file of all statements ever executed against the database (by default the file is called transaction-log-file.sql). dbtran dbfile.log

      • Replace dbfile.log with the path to the transaction log file

For more information on the Log Translation utility, see http://dcx.sybase.com/index.html#1200en/dbadmin_en12/dbtran.html.

  1. Start the database server:

    dbengX -n reload dbfile.db

    • X is 50, 6, 7, 8, 9, 10, 11, or 12
    • Replace dbfile.db with the path to the database file
  2. Using Interactive SQL, apply the SQL script file created by the Log Translation utility:

dbisql -c "UID=DBA;PWD=sql;ENG=reload" c:\transaction-log-file.sql

      •  Replace c:\transaction-log-file.sql with the path to the SQL script file created by dbtran.

For more information about Interactive SQL, see http://dcx.sybase.com/index.html#1200en/dbadmin_en12/dbisql-interactive-dbutilities.html

What are the considerations for replicating or synchronizing databases?

In a replication or synchronization environment, care must be taken with the transaction log and the transaction log offsets. When a database is rebuilt, the transaction log offsets are not the same as the original database, and this will affect these types of environments. For this reason, the database should not be forcefully started without the transaction log. If the database needs to be rebuilt, it should be done as follows:
dbunload -ar path-for-old-dbfiles -c "UID=DBA;PWD=sql;ENG=dbserver"
Alternatively, you can rebuild the database manually using the steps in

Note: This restriction does not apply to a MobiLink consolidated database because there is no dependency on the transaction log offsets. However, care should still be taken with the transaction log. This restriction does apply to MobiLink remote databases The ideal method for recovering from an assertion failure in an environment involved in replication or synchronization is using a tested backup and recovery strategy.

For more information on creating a backup and recovery strategy, see http://dcx.sybase.com/index.html#1200en/dbadmin_en12/understand-developing-backups.html.

Salvaging the database if there is no valid backup and the database cannot be started without an immediate assertion failure

If none of the above suggestions have worked to resolve the database assertion failure, then it is recommended that you open a Technical Support case with iAnywhere Solutions. Customers with a support plan can open a case using Case Express (accessed online at http://case-express.sybase.com/).

North American customers that do not want to use Case Express or do not have a support contract with iAnywhere Solutions can contact Technical Support by calling 1-800-8Sybase (1-800-879-2273). Customers that do not have a support plan will incur a fee for opening a case with Technical Support.

For customers outside of North America, information about contacting Technical Support is available at http://www.sybase.com/contactus/support#tech.

Back to Top
© Copyright 2010, Sybase Inc.