Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 17 » SQL Anywhere Server - SQL Reference » SQL statements » Alphabetical list of SQL statements


Preallocates space for a dbspace or for the transaction log, or updates the catalog when a dbspace file is renamed or moved.

 { ADD number [ add-unit ]
   | RENAME filename }
add-unit : 
| KB 
| MB 
| GB 
| TB
  • TRANSLOG clause

    You supply the special dbspace name TRANSLOG to preallocate disk space for the transaction log. Preallocation improves performance if the transaction log is expected to grow quickly. You may want to use this feature if, for example, you are handling many binary large objects (BLOBs) such as bitmaps.

    The syntax ALTER DBSPACE dbspace-name TRANSLOG RENAME filename is not supported.

  • TEMPORARY clause

    You supply the special dbspace name TEMPORARY to add space to temporary dbspaces. When space is added to a temporary dbspace, the additional space materializes in the corresponding temporary file immediately. Preallocating space to the temporary dbspace of a database can improve performance during execution complex queries that use large work tables.

  • ADD clause

    An ALTER DBSPACE statement with the ADD clause preallocates disk space for a dbspace. It extends the corresponding database file by the specified size, in units of pages, kilobytes (KB), megabytes (MB), gigabytes (GB), or terabytes (TB). If you do not specify a unit, PAGES is the default. The page size of a database is fixed when the database is created.

    If space is not preallocated, database files are extended by about 256 KB at a time for page sizes of 2 KB, 4 KB, and 8 KB, and by about 32 pages for other page sizes, when the space is needed. Pre-allocating space can improve performance for loading large amounts of data and also serves to keep the database files more contiguous within the file system.

    You can use this clause to add space to any of the predefined dbspaces (system, temporary, temp, translog, and translogmirror).

  • RENAME clause

    If you rename or move a database file other than the main file to a different directory or device, you can use ALTER DBSPACE with the RENAME clause to ensure that the database server finds the new file when the database is started. The filename parameter can be a string literal, or a variable.

    The name change takes effect as follows:

    • If the dbspace was already open before the statement was executed (that is, you have not yet renamed the actual file), it remains accessible; however, the name stored in the catalog is updated. After the database is stopped, you must rename the file to match what you provided using the RENAME clause, otherwise the file name won't match the dbspace name in the catalog and the database server is unable to open the dbspace the next time the database is started.

    • If the dbspace was not open when the statement was executed, the database server attempts to open it after updating the catalog. If the dbspace can be opened, it becomes accessible. No error is returned if the dbspace cannot be opened.

      To determine if a dbspace is open, execute the statement below. If the result is NULL, the dbspace is not open.

      SELECT DB_EXTENDED_PROPERTY('FileSize','dbspace-name');

      Using ALTER DBSPACE with RENAME on the main dbspace, system, has no effect. The RENAME clause is not supported for changing the name of the transaction log file. You can use the BACKUP DATABASE statement to rename the transaction log for a running database. For example:

      BACKUP DATABASE DIRECTORY 'directory-name'

Each database is held in one or more files. A dbspace is an additional file with a logical name associated with each database file, and used to hold more data than can be held in the main database file alone. ALTER DBSPACE modifies the main dbspace (also called the root file) or an additional dbspace. The dbspace names for a database are held in the SYSDBSPACE system view. The main database file has a dbspace name of system.

When a multi-file database is started, the start line or ODBC data source description tells the database server where to find the main database file. The main database file holds the system tables. The database server looks in these system tables to find the location of the other dbspaces, and then opens each of the other dbspaces. You can specify which dbspace new tables are created in by setting the default_dbspace option.


You must have the MANAGE ANY DBSPACE system privilege.

Side effects

Automatic commit.

  • ANSI/ISO SQL Standard

    Not in the standard.


The following example increases the size of the system dbspace by 200 pages:

ADD 200;

The following example increases the size of the system dbspace by 400 MB:

ADD 400 MB;

The following example changes the file name associated with the fictitious system_2 dbspace:

RENAME 'e:\db\dbspace2.db';