Sybase IQ Stops Processing or Stops Responding

You can detect the cause of server unresponsiveness by looking in the Sybase IQ message file.

Possible Causes

The following are the two most common causes of server unresponsiveness:

Insufficient disk space.
Insufficient room in main or temp buffer cache.

Action

If your server seems to be prone to unresponsiveness, either while processing or during shutdown, use the start_iq command line option -z and the Sybase IQ database option QUERY_PLAN = 'ON' to log useful information in the Sybase IQ message (.iqmsg) and server log (.srvlog) files.

In addition to logging this information, there are other steps you can take to determine the cause of the problem:

Check both the Sybase IQ message file and the server log file for You have run out of space... messages. If you have run out of IQ main store or IQ temporary store, add the appropriate dbspace with the CREATE DBSPACE command.

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.
Determine if the Sybase IQ server process (iqsrv15) 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 Sybase IQ server process should be processing normally.

If the Sybase IQ server CPU usage is normal, you can examine what the server is doing, i.e., what statement the server is currently executing.

If there are no out of space indications, use Interactive SQL on a new or existing connection to gather the following information, in the specified order.

Information to gather for server unresponsiveness
Command	Informational purpose
SELECT db_name()	Database name
CHECKPOINT	Checkpoint can succeed
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 the issue, contact Sybase Technical Support for assistance. They can use the information you have just gathered to help diagnose the problem.

When the server is unresponsive, you can generate a stack trace for each Sybase IQ thread by creating a file named DumpAllThreads or dumpallthreads in the $IQDIR15/logfiles directory (the %ALLUSERSPROFILE\%\SybaseIQ\logfiles folder on Windows 64 platforms, C:\ProgramData\SybaseIQ\logfiles for Vista 64).

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

The Sybase 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.
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;
Check near the end of the Sybase 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 secondary server to a multiplex
- Increase the processing capacity of the hardware by adding CPUs