Insufficient disk space

The Sybase IQ server does not wait for additional space on an out-of-dbspace condition, but instead rolls back either the entire transaction or rolls back to a savepoint. If there is not enough temporary or main dbspace available for a buffer or dbspace allocation request, then the statement making the request rolls back.

At this point, the DBA can add more space to a dbspace using the ALTER DBSPACE or the ALTER FILE command. (You may choose to add files instead of dbspaces. A single dbspace can have multiple dbfiles.)

WARNING! If Sybase IQ holds certain system locks or is performing a checkpoint when you run out of disk space, you may not be able to add disk space. For this reason, recognizing when you are low on disk space and adding a new dbspace before you run out of space are important.

For examples of using event handlers to monitor disk space usage, see the section “Monitoring disk space usage”.

Actions

Running out of space during checkpointing

Start in forced recovery mode and add space as soon as possible. You must add a dbspace before any new checkpoints can succeed. See “Starting servers in forced recovery mode”. For multiplex servers, see Using Sybase IQ Multiplex.

Effect of checkpoints on out of disk space conditions

If Sybase IQ has already run out of space when a checkpoint is requested, the checkpoint command fails with the error:

You have run out of space during the CHECKPOINT operation.
[EMSG_IQSTORE_OUTOFSPACE_CHECKPOINT:'QSB33', 1009133].

You must add a dbspace before any new checkpoints can succeed.

Adding space if you cannot connect to a server

If you run out of space during an operation and are unable to add space because you cannot connect to the server, you must:

  1. Shut down the database server using any of these methods:

    • On any platform, run dbstop.

    • On Windows, click the correct server icon on the Windows task bar to display the Sybase IQ window, and then click the Shutdown button.

    • On UNIX, run stop_iq or type q in the window where the server was started.

    If the server does not shut down, see “Server fails to shut down” below.

  2. Restart the engine with the start_iq command.

  3. Connect to the database.

  4. Use the CREATE DBSPACE command to add space.

  5. Rerun the operation that originally failed due to insufficient space.

Managing dbspace size

Growth of catalog files is normal and varies depending on application and catalog content. The size of the .db file does not affect performance, and free pages within the .db file are reused as necessary. To minimize catalog file growth:

If the catalog store cannot extend one of its files (.tmp.db, or .iqmsg), Sybase IQ returns the error A dbspace has reached its maximum file size. To prevent this problem:

Adding the wrong type of space

If the temporary dbspace runs out of space and you accidentally omit the TEMPORARY keyword in the CREATE DBSPACE command, you cannot create a temporary dbspace. Instead, add the file in the existing temporary dbspace as IQ_SYSTEM_TEMP.

Fragmentation

Sybase IQ provides control over fragmentation by taking advantage of even the smallest unused spaces. However, fragmentation can still occur. If your database runs out of space, even though Mem Usage listed by sp_iqstatus or the .iqmsg file shows Main IQ Blocks Used is less than 100%, it usually indicates that your database is fragmented,

Freeing space

Note that when a connection is out of space, freeing space by dropping tables or indexes in another connection is not possible, because the out of space transaction will see those objects in its snapshot version.

Monitoring disk space usage

Recognizing when the server is low on disk space and adding a new dbspace before the server runs out of space is important. See the section “Monitoring disk space usage” for examples of using event handlers to monitor disk space usage and to notify you when available space is low.

Reserving space for the future

Sybase IQ automatically reserves the minimum of 200MB and 50 percent of the size of the last dbspace.

To ensure that you have enough room to add new dbspaces if you run out of space in the future, set the database options MAIN_RESERVED_DBSPACE_MB and TEMP_RESERVED_DBSPACE_MB. Set these options large enough to handle running out of space during a COMMIT or CHECKPOINT. See Chapter 2, “Database Options,” in Reference: Statements and Options for details.