Sybase IQ stops processing or stops responding

Possible causes

• Insufficient disk space. See the section “Insufficient disk space” for actions to take.

• Insufficient room in main or temp buffer cache. See “Managing buffer caches” in Chapter 4, “Managing System Resources” of the Sybase IQ Performance and Tuning Guide.

Action

• Check both the IQ message file and the server log file for “You have run out of … dbspace” messages. If you have run out of IQ STORE (main) or IQ TEMPORARY STORE, add the appropriate dbspace with the CREATE DBSPACE command. See the section “Insufficient disk space” for more information on resolving out of space issues.

  Setting the database options MAIN_RESERVED_DBSPACE_MB and TEMP_RESERVED_DB_SPACE_MB to large enough values to handle running out of space during a DDL COMMIT or CHECKPOINT is also important. A few hundred MB should be enough, but these options can be set higher for a large database. For more information, see the sections “Handling out-of-space conditions for DDL commands” and “Reserving space to handle out-of-space conditions” in Chapter 5, “Working with Database Objects” of the Sybase IQ System Administration Guide.

• Determine if the IQ server process (asiqsrv12) is consuming CPU cycles by monitoring the CPU usage for a few minutes at the operating system level. Record this information. If the CPU usage changes, then the IQ server process should be processing normally.

  If the IQ server CPU usage is normal, you can examine what the server is doing, i.e., what statement the server is currently executing. For details on capturing this information and logging server requests, see the sections “Finding the currently executing statement” and “Logging server requests”.

• If there are no out of space indications, use dbisql on a new or existing connection to gather the information listed in the following table (in this order).

  Table 1-1: Information to gather for server unresponsiveness

  Command	Informational purpose
  select db_name()	database name
  checkpoint	checkpoint can succeed
  select db_property('FileVersion')	version of catalog
  set temporary option truncation_length=100	increase output line length
  sa_conn_properties ># sa_conn_properties.out	connection information
  sa_conn_info ># sa_conn_info.out	connection information
  sa_db_properties ># sa_db_properties.out	database property information
  sa_eng_properties ># sa_eng_properties.out	server property information
  sp_iqstatus ># sp_iqstatus.out	database status information
  sp_iqconnection ># sp_iqconnection.out	connection information
  sp_iqtransaction ># sp_iqtransaction.out	transaction information

  If you cannot resolve this issue, contact Sybase Technical Support for assistance. The information you have just gathered can be used by Technical Support to help diagnose the problem. See the section “Reporting problems to Technical Support”.

• When the server is unresponsive, you can generate a stack trace for each IQ thread by creating a file named DumpAllThreads or dumpallthreads in the $ASDIR/logfiles directory (the %ASDIR%\logfiles folder on Windows platforms).

  Starting Sybase IQ as recommended, using the Program Manager or start_asiq command, sets the ASDIR variable automatically. If the ASDIR variable is not set, create the DumpAllThreads file in the directory in which asiqsrv12 was started.

  The IQ server detects the presence of the DumpAllThreads file and writes a stack trace for each IQ thread in the stack trace file stktrc-YYYYMMDD-HHNNSS_#.iq. After the stack traces are written to the stack trace file, the DumpAllThreads file is deleted.

  This stack trace information can be used by Sybase Technical Support to help diagnose the problem. See the section “Reporting problems to Technical Support”.

• If you can connect to the database, run the IQ UTILITIES buffer cache monitor on the main and temp (private) buffer caches for 10 minutes with a 10 second interval:

  1. Connect to the database or use the existing connection.

  2. CREATE TABLE #dummy_monitor(c1 INT);

  3. IQ UTILITIES MAIN INTO #dummy_monitor START MONITOR '-append -debug -interval 10 -file_suffix iqdbgmon';

  4. IQ UTILITIES PRIVATE INTO #dummy_monitor START MONITOR '-append -debug -interval 10 -file_suffix iqdbgmon'; Let the process run for 10 minutes, then stop the buffer cache monitor:

  5. IQ UTILITIES MAIN INTO #dummy_monitor STOP MONITOR;

  6. IQ UTILITIES PRIVATE INTO #dummy_monitor STOP MONITOR;

  For more information on monitoring buffer caches, see the section “Monitoring the buffer caches” in Chapter 5, “Monitoring and Tuning Performance” of the Sybase IQ Performance and Tuning Guide.

• Check near the end of the IQ message file for the message "Resource count 0", which may be followed by an "Open Cursor" message. These messages indicate a resource depletion, which can cause a deadlock. The immediate solution is to reduce the number of active connections using CTRL-C or the DROP CONNECTION command.

  The long term solution to avoid a deadlock due to resource depletion is one or a combination of the following:

  • Restrict the number of users on the server by reducing the value of the -gm server startup option

  • Add another query server to a multiplex

  • Increase the processing capacity of the hardware by adding CPUs

---

Copyright © 2004. Sybase Inc. All rights reserved.

View this document as PDF