SQL Anywhere service started and then stopped

• 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

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.