Sybase 12.4.2 manual Dropping dbspaces, 116

ADD DBSPACE

Working with databases

Reserving space for DDL commands

In the event that you run out of space to perform an operation, you will see a message telling you to more space. In addition to space for the new dbspace itself, you also need a small amount of space to issue the

command. To ensure that you have the space for this and related DDL commands, set the options MAIN_RESERVED_DBSPACE_MB and TEMP_RESERVED_DBSPACE_MB. Do not wait until you have run out of space to set these options. See the “Database Options” chapter of the Adaptive Server IQ Reference Manual for option details.

Dropping dbspaces

You can issue a DROP DBSPACE command to remove a database file. In order to drop a dbspace, the following must be true:

•It must not contain any data. Adaptive Server IQ does not allow you to drop a dbspace unless it is empty.

•It must be the last one added. (After you drop the last dbspace, the next most recently added dbspace becomes the last one, and can be dropped.)

•It must not be one of the four initial dbspaces, SYSTEM,

IQ_SYSTEM_MAIN, IQ_SYSTEM_TEMP, and IQ_SYSTEM_MSG. These dbspaces can never be dropped.

Because of the way Adaptive Server IQ fills dbspaces with data, it is unlikely that you will be able to drop the last dbspace, especially if disk striping is in use. You also cannot empty a dbspace by truncating the tables in it, as even an empty table takes some space. The only way to completely remove a table and its data is with a DROP TABLE statement (or by dropping the table in Sybase Central).

If you drop or truncate a table while other users are reading from it, the normal rules of table versioning apply, that is, old table versions remain until readers' transactions complete; see Chapter 8, “Transactions and Versioning” for details.

To find out whether you can drop a particular dbspace, run the stored procedure sp_iqstatus. Look at the DB Blocks value, which tells you the block numbers each dbspace includes. Compare this value to the Main IQ Blocks Used (or Temporary IQ Blocks Used), to see whether the Max Block # is in the dbspace. If it is, you cannot drop this dbspace.

116