SQL Anywhere service started and then stopped

Modified on: Tue, 8 Sep, 2020 at 11:05 AM

[Sybase][ODBC Driver][SQL Anywhere]Internal database error *** ERROR ***Assertion failed: 200114 ()[BI4_CMS]Can't find values for row 18614:5 in index 'ObjName_I7' -- transaction rolled backSQLCODE=-301

SQLSTATE=HY000

• SAP SQL Anywhere - all versions
• SAP BusinessObjects Business Intelligence platform 4.1, 4.2 - SP2-SP4

Reason

SQLA Bug Fix ( QTS 790977 - Under very rare timing dependent condition an index that has long hash values could throw assertions 200114 (cannot find values) or invalid page assertion. This has been fixed. ):Assertion 200114, Can't find values for row ... in index ..

How to resolve

• Make sure to have userid/password available for the CMS database
• Please note that this example uses SQL Anywhere version 12 as an example. If you are running version 16 or 17, please change 12 to 16 or 17 accordingly.
  For example, if you are using SQL Anywhere 16, you will need to put dbsrv16 instead of dbsrv12 in all following commands.

There are 3 possible workarounds to this issue:

workaround1: A possible solution is to rebuild the index using:

• Make sure CMS service is not running.
• Copy CMS database (BI4_CMS.db , BI4_CMS.log ) to temp directory e.g. C:\temp )
• Open cmd window and change directory to c:\temp directory : cd c:\temp
• Start CMS database from cmd prompt with the following command : dbsrv17 -hW EnableCleaner -hV -n REBUILD c:\temp\BI4_CMS.db
• Connect to started database with dbisql , e.g.: dbisql -c "uid=dba;pwd=;ENG=REBUILD;DBN=BI4_CMS" -nogui
• Run ALTER INDEX ... REBUILD command on CMS_InfoObjects7 index :
  ALTER INDEX ObjName_I7 ON dba.CMS_InfoObjects7 REBUILD
• Type the command Exit - to exit out the dbisql window
• Stop the database:
  dbstop -c "Server=REBUILD;uid=dba;pwd="
• Start the database with default switches : dbsrv17 -n REBUILD c:\temp\BI4_CMS.db
• Connect with dbisql again  : dbisql -c "uid=dba;pwd=;ENG=REBUILD;DBN=BI4_CMS" -nogui 
• Type the follwing command: call sa_clean_database()
• If above procedure i) completes without errors, then stop REBUILD server
• Type the command Exit - to exit out the dbisql window
• Stop the database:
  dbstop -c "Server=REBUILD;uid=dba;pwd="
• Backup and then replace BI4_CMS.* files in CMS directory with the CMS files from c:\temp\BI4_CMS.*
• Restart CMS service.

workaround2: Drop and recreate the index :

• Same as in 1) ,except in step f) instead of rebuilding index , drop and recreate it with following step

DROP INDEX IF EXISTS "dba"."CMS_InfoObjects7"."ObjName_I7";

CREATE INDEX "ObjName_I7" ON "dba"."CMS_InfoObjects7" ( "ObjName" ASC ) IN "system";

workaround 3: Rebuild corrupted database please follow steps below: 

• Create a backup of your current (corrupted) database by copying it to another folder e.g. :
• copy BI4_CMS.db BI4_CMS.log c:\temp
• Start corrupted database with options which will disable database cleaner and disable automatic events :
• dbsrv12 -hW EnableCleaner -hV -n REBUILD c:\temp\BI4_CMS.db
• Rebuild the database using "unsorted" option e.g. :
• dbunload -u -c "uid=dba;pwd=;eng=REBUILD" -ar
• Right click on 'Shutdown' on dbsrv12 to stop the server (check icon tray for SQL Anywhere )
• Start newly created database to verify if it starts without errors :e.g.
• dbsrv12 c:\temp\BI4_CMS.db
• Right click on 'Shutdown' on dbsrv12 to stop the server (check icon tray for SQL Anywhere )
• Replace corrupted database and transaction log with newly created c:\temp\BI4_CMS.db and c:\temp\BI4_CMS.log

• Note: It might take longer than usual to restart the BI system for the first time after applying these changes.
• To avoid this assertion in the future upgrade SAP SQL Anywhere to build :
  • Upgrade to SAP BusinessObjects 4.2 SP6 or SAP Crystal Server SP6 to get the updated SAP SQL Anywhere 17.0.8 Build 4103 or higher

Did you find it helpful? Yes No

Send feedback

Sorry we couldn't be helpful. Help us improve this article with your feedback.

Mel, It could be that the server that was running got hung. Use Task Manger to teminiate all DBSRV11.EXE (or DB Eng...etc). If you are unable to terminate the hung service via task manger then download PSTOOL and use PSKILL ServiceName. Hope this help... Rachan Terrell wrote in message news:... > Ok, thank you. I located the event log via your > instructions. > > When trying to strt the service the following three events > are logged (One information event and twi error events): > > 1. Info: SQLANYs_CHSDataSvc: Starting service > SQLANYs_CHSDataSvc > 2. Error: SQLANYs_CHSDataSvc: A database server with that > name has already started > 3. Error: SQLANYs_CHSDataSvc: Could not start server > > Thanks, > Mel > > > > >> Event logging is provided by the OS. >> >> Go to Control Panel | Adminstrative Tools | Event Viewer >> and look at "Application" log. >> >> -chris >> >> Mel wrote: >> > Hi Chris, >> > >> > I haven't been able to locate an existing log. Is there >> > a standard file path in SQL Anyhere 10 where this is >> > stored? >> > I also tried specifying my own file path like this: >> > C:\ProgramFiles\SQL Anywhere 10\AppLog\sybaseLog.txt >> > >> > But no output was logged to the file when trying to >> > start the service. >> > >> > Thanks, >> > Mel >> > >> > >> >> What is logged in the event application log? It should >> >> provide some info. If it does not, add -o >> to >> your parameters so that we can see what the console >> has >> logged. >> >> >> >> -chris >> >> >> >> >> >> Mel wrote: >> >>> Hi, >> >>> >> >>> We have a Virtual Machine that is running Windows >> Server >>> 2008 R2. On this server we have SQL Anywhere >> 10.0.1 >>> installed. We have setup a service with the >> following >>> parameters: >> >>> >> >>> -c 40M >> >>> -n XXXDataSvc >> >>> C:\XXX\xxxData.db >> >>> >> >>> When we try and start the service Sybase freezes and >> has >>> to be closed. When Sybase is re-opened the status >> of the >>> service reads "Start Pending". We are able to >> open the >>> database in Sybase so we are sure there is no >> problems >>> with the db file itself. Also this exact >> setup was >>> working on our old server, which is running >> Windows >>> Server 2003. >> >>> Any help or ideas on solving this issue would be >> really >>> appreciated. >> >>> >> >>> Thank you, >> >>> Mel

If you start a service, it keeps running until you stop it. Closing Sybase Central or logging off does not stop the service.

Stopping a database server service closes all connections to the database and stops the database server. For other applications, the program shuts down.

1. From the Context dropdown list, click SQL Anywhere 12.

2. In the right pane, click the Services tab.

3. Select the service, and then click File » Start or Stop.

This Technote addresses an issue where the SQL Anywhere database service will not start from IBM® Rational® Administrator or Services in Control Panel.

The version of SQL Anywhere that ships with IBM Rational ClearQuest® 2001 and 2002 is 5.5.05 Build 2509. SQL Anywhere 5.5.05 is supported by Sybase for Windows 2000 but only if it is Build 2785 or later. Some setups with Build 2509 have the problem that the service will not start. Go to http://www.sybase.com/downloads to download Build 2785 or later.

Sometimes installing this later build does not solve the problem either. If the 5.5.05 server is allowed to broadcast during start-up to ensure its name is unique (the default behavior), it will fail to start on Windows 2000. The error is "unable to initialize requested communications links". A work-around to the problem is to use DoBroadcast=no when specifying the TCPIP parameter on the server start line.

To add this line click:

1. Goto Start  > Programs  > Sybase SQL Anywhere database server 5.5.05  > Sybase Central
   (You should see a "Services" and "Utilities" folder)
2. Click on the "Services" folder
   (You should see in the right window "Add Service" and the name of your SQL Anywhere database server)
   Note: For this example we'll use SQL_Server as the server name.
3. Double click your database server to bring up the properties
4. Goto the "Configuration" tab.
5. Look at the lower section called "Parameters for executable".
6. You should see a line similar to this:
   -n SQL_Server -c 2048 -tl 0 -ti 7200  -x TCPIP
7. The SQL_Server in this example should be the name you gave your database server when you created it.
8. Now add the DoBroadcast=no Once you add this the line should look like the one below...

   -n SQL_Server -c 2048 -tl 0 -ti 7200  -x TCPIP{DOBROADCAST=NO}

   For more information, contact IBM Rational Software Technical Support.

[{"Product":{"code":"SSSH5A","label":"Rational ClearQuest"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"Database Configuration\/Connectivity - SQL Anywhere","Platform":[{"code":"PF033","label":"Windows"}],"Version":"2001a.04.00;2002.05.00;2002.05.20","Edition":"","Line of Business":{"code":"LOB45","label":"Automation"}}]