Handling an Assertion Failure
This document describes steps you can take to recover your database when you receive an assertion failed message. It also includes a checklist of information that is required if you need to contact Technical Support about your assertion failure.
This document applies to Adaptive Server Anywhere versions 5.5.03 through 9.0.2. All documentation references are from the SQL Anywhere Studio 9.0.2 documentation. For further assistance with assertion failures, it is recommended that you call 1-800-8Sybase and open a Technical Support case or post questions to the SQL Anywhere newsgroups. Information concerning the SQL Anywhere newsgroups is available at http://www.sybase.com/detail_list?id=10891.
What is an assertion failure?
Adaptive Server 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 Server Messages window or output log will report the following:
*** ERROR *** Assertion failed: 123456 (x.0.x.xxxx)
Error message follows with information about the cause of the assertion.
Note: Assertion errors may differ depending on the version of SQL Anywhere Studio. There are slight variations in the error messages that are provided during an assertion failure with each release of SQL Anywhere Studio. It is always beneficial to note all the information given in the assertion error message.
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, the database server terminates all current connections, refuses any new connections, and then stops the database when an unexpected condition is encountered.
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?
- Record the assertion number and message that appear in the Server Messages window or log file. The assertion number and message are important in attempting to determine the cause of the assertion. Technical Support needs to know this information if their assistance is required.
- Shut down the database server, if it is still running. This is very important if multiple databases are running on a single database server. This will protect the other databases running on the database server from potential corruption.
- 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). Old backup copies of the database file and transaction logs should not be overwritten. It is important to maintain the database file and transaction log in the state immediately after the assertion failure for recovery purposes.
- Attempt to restart the database server and load the database file.
- 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 have no 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 preexisting corruption in the database files is to rebuild the database using the unload/reload process. Restarting the database alone never ensures that there is no corruption in the database file.
For more information about the Validation utility, see http://download.sybase.com/pdfdocs/awg0900e/dbdaen9.pdf - Chapter 15, Database Administration Utilities.
For more information about syntax for the dbvalid utility, see http://download.sybase.com/pdfdocs/awg0900e/dbdaen9.pdf- Page 548.
For more information about the sa_validate system procedure, see http://download.sybase.com/pdfdocs/awg0900e/dbrfen9.pdf - Page 745.
- 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 repairing any problems can be completed, 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 environment 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, or 9
- 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 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 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://download.sybase.com/pdfdocs/awg0900e/dbdaen9.pdf - Chapter 12
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 done as part of an ideal 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 for testing the validity of the database. These tests need to be run on a copy of the backup or with the database server in read-only mode because the Validation utility changes the database offsets.
If the integrity of the backup has not already been validated, use a copy of the backup to determine whether the backup copy of the database is valid using the Validation utility (dbvalid). This process detects a majority of invalid databases, but there is still a chance that subtle corruption exists in the database. To ensure the backup database is not corrupt, run dbunload and create 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 database, see http://download.sybase.com/pdfdocs/awg0900e/dbugen9.pdf - Page 543.
For more information about rebuilding a database manually, see http://download.sybase.com/pdfdocs/awg0900e/dbugen9.pdf - Page 539.
Now that the backup of the database has been validated, restore another copy of the backup. You will 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, or 9
- 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.
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 synchronizing or replicating environment, the applied transaction logs do not need to be retained.
Recovering if there are no valid backups, but a valid transaction log exists from day 1 of the database
Adaptive Server 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.
To recover the database with this process
- Create a new database with the same initialization parameters as the existing database using the Initialization utility. 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 Adaptive Server Anywhere 8.0.0 or higher. If you suspect you are using Java in the database with Adaptive Server 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://download.sybase.com/pdfdocs/awg0900e/dbdaen9.pdf - Page 485.
For more information about the Information utility, see http://download.sybase.com/pdfdocs/awg0900e/dbdaen9.pdf - Page 483.
- Make a backup copy of the transaction log in case there are any problems that occur while attempting to translate the transaction log.
- Translate the transaction log using the Log Translation utility.
This creates a SQL script file of all statements ever executed against the database (by default the file is called transaction-log-file.sql).
- Replace dbfile.log with the path to the transaction log file
For more information on the Log Translation utility, see http://download.sybase.com/pdfdocs/awg0900e/dbdaen9.pdf - Page 508.
- Start the database server:
dbengX -n reload dbfile.db
- X is 50, 6, 7, 8, or 9
- Replace dbfile.db with the path to the database file
- 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://download.sybase.com/pdfdocs/awg0900e/dbdaen9.pdf- Page 492
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 c:\path-for-old-dbfiles -c "UID=dba;PWD=sql;ENG=dbserver"
Alternatively, you can rebuild the database manually using the steps in http://download.sybase.com/pdfdocs/awg0900e/dbsren9.pdf - Page 249.
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 does apply to MobiLink remote databases.
The ideal method for recovering from an assertion 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://download.sybase.com/pdfdocs/awg0900e/dbdaen9.pdf - Page 337.
Salvaging the database if there is no valid backup and the database starts but asserts frequently
If the database can be started successfully and only asserts when certain operations are executed against the database, then the cause of the assertion may be a corrupt index or table. This type of corruption can often be resolved by rebuilding the database, working around the corrupted index or table.
For a database that has a corrupt index, the assertion message may look similar to the following:
*** ERROR *** Assertion failed: 100305 (184.108.40.20624)
Invalid index page encountered during an index scan @1
(table id 12, page 0x1a8)
This message shows just one potential error than can result from a corrupt index. A corrupt index can cause a multitude of assertion error messages.
The assertion error message may provide enough information to determine the specific index that is corrupt. If this is the case:
- Make a backup copy of the database file and transaction log.
- Drop and recreate the index that has been identified as corrupt.
- Validate the database.
- If the database validates with no errors, then the index has been corrected.
If the corrupt index cannot be identified or the above steps cause another database assertion, the next step is to attempt an unordered rebuild of the database. If the above steps have been attempted, revert to the backup copy of the database file and transaction log before attempting an unordered rebuild. To attempt and unordered rebuild run the following command to unload the database where X is one of 50, 6, 7, 8, or 9, and dbfile.db is the path to the database file:
dbunload -u -ar c:\path-for-old-dbfiles -c "UID=dba;PWD=sql;DBF=c:\dbfile.db"
Alternatively, you can run the following commands to unload the database:
dbunload -u -c "UID=dba;PWD=sql;DBF=c:\dbfile" c:\unload
dbengX -n new newdbfile.db
Run the following command to apply the reload.sql file:
dbisql -c "UID=dba;PWD=sql;ENG=new" c:\reload.sql
- Replace reload.sql with the path to the SQL script file created by dbunload
For a database that has a corruption in a table, an assertion message will look similar to the following:
*** ERROR *** Assertion failed: 201501 (220.127.116.1124)
Page for requested record not a table page or record not present on page
The first step in recovering from an assertion failure that is caused by table corruption is to determine the table or tables that have corruption. This can be done by validating the database using the Validation utility.
For more information on The Validation Utility please see: http://download.sybase.com/pdfdocs/awg0900e/dbdaen9.pdf - Chapter 15
After determining the table or tables that are corrupted, follow the steps in the document "Salvaging Data When There are Corrupt Pages in the Database".
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, 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.
iAnywhere Solutions has a data salvage service for the salvage of data from a corrupt database. This is a paid service, accessed by opening a Technical Support case. The fee for the data salvage is in addition to any Technical Support contract or case fees. The data salvage service requires a signed agreement between the company opening the Technical Support case and iAnywhere Solutions. The contract specifies that iAnywhere Solutions makes no guarantee as to the amount of data salvaged, if any, its integrity, or the time required to salvage any data during the service. This service should be used as a last resort if all other options have been exhausted. The best way to protect the data in a database is through a tested backup and recovery strategy. The data salvage service for the salvage of data from a corrupt database is not intended to be a substitute for a tested backup and recovery strategy.
If you need to send in your database for data salvage, Technical Support needs the answers to as many of the following questions as possible. This information will help in salvaging your database as efficiently as possible and track potential causes of corruption.
Corrupt database checklist
- What are the DBA (or DBA authority) username and password?
- What is the exact assertion error message number and description?
The exact assertion error message number and description is important in determining the potential cause of the assertion. Determining the cause of the assertion will provide sufficient information for determining the steps to take to handle the assertion.
- What version of the software was used to create this database?
If the assertion is a result of a bug in the software related to the version of the software used to create the database, potentially the problem has been resolved. This information can drastically reduce case resolution time in determining the cause of the assertion.
- What version and build of Adaptive Server Anywhere is the database server? What operating system, including version and build, is the database server running on?
Knowing the version and build of Adaptive Server Anywhere is important in determining whether this assertion is related to a software bug. If the database server is not the most recent EBF, then it is recommended that you read through the README file for the latest EBF to determine if there is a software bug that may causing the database assertion. A Technical Support representative will need to know the operating system, version and build of Adaptive Server Anywhere to diagnose the cause of the database assertion.
- Include dbinfo output if possible (from a backup if necessary).
If you are using Adaptive Server Anywhere version 6, 7, 8, or 9, use the following command to obtain the dbinfo output, substituting the appropriate user ID, password, and database file name for your database:
dbinfo -o c:\info.txt -c "UID=dba;PWD=sql;DBF=c:\asademo.db"
If you are using SQL Anywhere Studio version 5.x.x, use the following command to obtain the dbinfo output:
dbinfo -o c:\info.txt asademo.db