Friday, May 20, 2016

SQL Anywhere Assertion Failed Error – Cause and Fixes

You are working on SQL Anywhere database and suddenly your database gets corrupt due to software related errors. An assertion failed error message comes up which reads as: *** ERROR *** Assertion failed: ###### (xx.x.x.xxxx). What are you going to do next?

Assertion failed errors are a commonplace these days when working on SQL Anywhere database. While you think that restarting the server would fix the error, however, this may not be the recommended solution. Let’s look at how we can fix SQL Anywhere assertion failed errors:

SQL Anywhere database can show the assertion failed error due to several reasons like hardware or software failure, unexpected operations etc. This error works as an indicator of issues in database. Let’s discuss more about this error.

What is an Assertions failed error?

Assertions are checked conditions in the SQL Anywhere database, which helps prevent database during corruption. when an assertion error occurs then database server stops the processing of client request and return an error message. It happens in SQL Anywhere 12 and lower versions.

In this error message, a six-digit number is present which is the assertion number, and every error message is associated with an assertion number. This number helps find the actual reason of the error. Some numbers following in the bracket to an assertion number indicates the version and build of the database server that asserted.

Given below are more details about the error (in text form):

The basic syntax of Assertion failed error is:

*** ERROR *** Assertion failed: ###### (xx.x.x.xxxx)
Cause of the assertion failed error

Some examples of assertion failed errors are given below:
*** ERROR *** Assertion failed: 101412 (xx.x.x.xxxx)
Page number on page does not match page requested

*** ERROR *** Assertion failed:100903 (xx.x.x.xxxx)
Unable to find table definition for table referenced in transaction log -- transaction rolled back

*** ERROR *** Assertion failed: 201116 (xx.x.x.xxxx)
Invalid free list index page found while processing checkpoint log -- Transaction rolled back

*** ERROR *** Assertion failed: 201819 (xx.x.x.xxxx)
Checkpoint log: invalid Bitmap Page - Transaction rolled Back


*** ERROR *** Assertion failed: 201819 (xx.x.x.xxxx)
Page Number ON Page Page requested does Not match - Transaction rolled Back


*** ERROR *** Assertion failed: 200502 (xx.x.x.xxxx)
ON Checksum failure Page 23 - Transaction rolled Back

How to deal with assertion failed errors?

  1. Note down the assertion number from a log file or message window of the database server to determine the actual cause of the assertion.
  2. In case of SQL Anywhere 16 database server assertion, shut down the database server immediately if server is still running. This, in turn, will protect other users who are working on the same server.
  3. Always take a backup copy of your database (.db file) and transaction log (.log file) because you can restore the database from backup. It is also very helpful for analysis purpose to know the actual reason of the error.
  4. Try to restart the database server with database files.

How to handle corruption in SQL Anywhere database?

  • The best way to deal with database corruption is database backup. If you do not have log backup then, you can try to start the database without the transaction log file. 

Note: This option only works when; the database is not involved in replication or synchronization.

  • If you have a log file, then without missing any details then there will be no data loss in the assertion failed situation. To recover the database, shut down the database, rename the old log file and restart your database by using the following command:

dbengX  -f  database.db
where X is the version of database.

  • Automatic recovery process: When database server shutdown normally then the, database server performs a checkpoint operation so that all the information of database kept in the database file. it is called clean shutdown. When you start database then, database server checks the reason of the last shutdown. If it is not clean then, database server performs the following steps to recover the database from failure:
    • Recover most recent checkpoint
    • Apply changes made since the checkpoint
    • Roll back the uncommitted transactions 

  • Using a third party software: If your database file (.db) is corrupted and you tried all the options to repair it but, file id still corrupted then, you should try Stellar Phoenix Repair to SQL Anywhere software to fix the corruption of .db file. It worked on SQL Anywhere versions 9.x, 10.x, 11.x, 12.x, and 16.x.

The Final Word: The above are the recommended solutions for fixing the assertion error in SQL Anywhere database; however, it is essential to, know the exact reason of error and then using the correct steps to fix it. Always take a backup of your data as this serves as a contingency plan in case of data loss. recover the . I hope these methods have been helpful. In case you have any comments or suggestions then, you can share with me.

No comments:

Post a Comment