The SAP Sybase IQ server does not
wait for additional space on an out-of-dbspace condition, but 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, 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 SAP 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. It is important for you to recognize when you are low
on disk space, and to add a new dbspace before you run out of space.
Actions
- Check recent messages in the SAP Sybase IQ message log (dbname.iqmsg). An out of space
message indicates that you must add another dbspace. The message in the
SAP 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 SAP Sybase IQ message log for the following messages:
- If a buffer or dbspace allocation request fails
because there is no space in the dbspace, this 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.
- If the entire transaction is rolled back on an
out-of-dbspace condition, you see:
%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, you see this message, 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, 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 SAP 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 SAP Sybase IQ is probably not operational. If the CPU usage
does change, SAP Sybase IQ is
operational.
- Check the sp_iqstatus output for:
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, 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,
indicating that 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.