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 an example of using an event handler to monitor disk space usage, see the section “Monitoring disk space usage”.
Check recent messages in the IQ message log (dbname.iqmsg). If you see an “out of dbspace” message, you must add another dbspace. The message in the IQ message file indicates which dbspace has run out of space and the minimum number of megabytes to add. If the problem occurs while you are inserting data, you probably need more room in the IQ Store. If the problem occurs during queries with large sort/merges, you probably need more room in the Temporary Store.
Check the IQ message log for the following 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.
[EMSG_IQSTORE_OUTOFDISK_HEADER: 'QSB31',1009131].
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 IQ is probably not operational. If the CPU usage does change, 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 Main IQ Store.
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.
If you run out of space during an operation and are unable to add space because you cannot connect to the server, you must:
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_asiq 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.
Restart the engine with the start_asiq command.
Connect to the database.
Use the CREATE DBSPACE command to add space.
Rerun the operation that originally failed due to insufficient space.
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:
Avoid using IN SYSTEM on CREATE TABLE statements.
Issue COMMIT statements after running system stored procedures.
Issue COMMIT statements after long-running transactions
If the Catalog Store cannot extend one of its files (.tmp, .db, or .iqmsg),
IQ returns the error A dbspace has reached its
maximum file size. To prevent this problem:
Monitor space usage periodically.
Verify that there are no operating system file size limits (such as Sun Solaris ulimit) where the .tmp, .db, or .iqmsg files are located. The .db and .tmp files are typically in the main IQ database directory. The .tmp file is located under $ASTMP/<servername>/tmp, or if $ASTMP is not set, under /tmp/.SQLAnywhere/<servername>/tmp.
If the temporary dbspace runs out of space and you accidentally omit the temporary keyword in the create dbspace command, the create dbspace command waits for you to add space to the temporary dbspace.
To provide the temporary dbspace needed by the create dbspace command, connect to the database from a new connection and create the temporary dbspace. Once the temporary dbspace is created, the create dbspace for main completes and all waiting connections resume running.
The same is true, if the main dbspace runs out of space and you attempt to add temporary dbspace. IQ waits for you to add space to main first, before adding to the temporary dbspace. You must add space to the dbspace that runs out of space first, and IQ ensures this sequence.
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,
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.
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 an example of using an event handler to monitor disk space usage and to notify you when available space is low.
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 the Sybase IQ Reference Manual for details.
