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.

Actions

Check recent messages in the Sybase IQ message log (dbname.iqmsg). If you see an out of space message, you must add another dbspace. The message in the Sybase IQ message file indicates which dbspace has run out of space. If the problem occurs while you are inserting data, you probably need more room in the IQ main store. If the problem occurs during queries with large sort-merge joins, you probably need more room in the IQ temporary store.
Check the Sybase IQ message log for the following messages:
- If a buffer or dbspace allocation request fails because there is no space in the dbspace, the following error message is logged in the dbname.iqmsg message file:
```
You have run out of space in %2 DBSpace. %1
```
```
[EMSG_OUT_OF_DBSPACE: SQL Code -1009170L,
SQL State QSB66, Sybase Error Code 20223]
```
  where %2 is the name of the dbspace.
  This error messages replaces the error message You have run out of { IQ STORE | IQ TEMPORARY STORE } dbspace in database <dbname>. In another session, please issue a CREATE DBSPACE ... { IQ STORE | IQ TEMPORARY STORE } command and add a dbspace of at least nn MB.
- If the entire transaction is rolled back on an out-of-dbspace condition, the following error message is reported:
```
%1 -- Transaction rolled back"
```
```
[IQ_TRANSACTION_ROLLBACK: SQL Code -1285L,
SQL State 40W09, Sybase Error Code 2973]
```
  where %1 is the error that caused the transaction to roll back, when encountered by the server during a critical operation.
- If a buffer allocation request finds a dirty buffer, but the buffer manager cannot flush the buffer due to an out-of-space condition, the following error message is returned and the current statement rolls back:
```
%2: All buffer cache pages are in use, ask your
DBA to increase the size of the buffer cache. %1
```
```
[EMSG_BUFMAN_ALLSLOTSLOCKED: SQL Code -1009031L,
SQL State QSA31, Sybase Error Code 20052]
```
  where %2 is the particular buffer cache throwing the exception.
Try to connect to the database from a new connection. If this works, you know that the database server is running, even though the query is waiting. Run sp_iqstatus to get more information.
If you cannot connect to the database, check if Sybase IQ is in an unusable state by monitoring the CPU usage for that processor. If the CPU usage does not change over a small time interval, then Sybase IQ is probably not operational. If the CPU usage does change, Sybase IQ is operational.
Check the sp_iqstatus output for the following two lines:
```
Main IQ Blocks Used:,10188 of 12288,
82%, Max Block#: 134840
```
```
Temporary IQ Blocks Used:,163 of 6144,
2%, Max Block#: 97
```
If the percentage of blocks used is in the nineties, you need to add more disk space with the CREATE DBSPACE command. In this example, 82% of the Main IQ Blocks and 2% of the Temporary IQ Blocks are used, so more space will soon be needed in the IQ main store.
If out-of-space conditions occur or sp_iqstatus shows a high percentage of main blocks in use on a multiplex server, run sp_iqversionuse to find out which versions are being used and the amount of space that can be recovered by releasing versions.

Running out of Space During Checkpointing
Start in forced recovery mode and add space as soon as possible.
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 an error.
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, add space using the CREATE DBSPACE command.
Managing Dbspace Size
Growth of catalog files is normal and varies depending on application and catalog content.
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.
Fragmentation
Sybase IQ provides control over fragmentation by taking advantage of even the smallest unused spaces.
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.
Reserving Space for the Future
Sybase IQ automatically reserves the minimum of 200MB and 50 percent of the size of the last dbspace.

Parent topic: Resource Issues

Related concepts

IQ Main Store and IQ Temporary Store Space Management

IQ_SYSTEM_MAIN Dbspace

Load Performance During Database Definition

Main IQ Store Blocks Message

Monitoring Disk Space Usage

Processing Issues

Sizing Guidelines for Main and Temporary Stores

Sybase IQ Stops Processing or Stops Responding