Overview of database files

Each database has the following files associated with it:

The database file This file holds the database information. It typically has the extension .db.
The transaction log This file holds a record of the changes made to the database, and is necessary for recovery and synchronization. It typically has the extension .log. See The transaction log.
The temporary file The database server uses the temporary file to hold information needed during a database session. The database server discards this file once the database shuts down—even if the server remains running. The file has a server-generated name with the extension .tmp.
The location of the temporary file can be specified when starting the database server using the -dt server option. If you do not specify the location of the temporary file when starting the database server, the following environment variables are checked, in order:
- SATMP environment variable
- TMP environment variable
- TMPDIR environment variable
- TEMP environment variable
If none of these are defined, SQL Anywhere places its temporary file in the current directory on Windows operating systems, or in the /tmp directory on Unix.

The server creates, maintains, and removes the temporary file. You only need to ensure that there is enough free space available for the temporary file. You can obtain information about the space available for the temporary file using the sa_disk_free_space procedure. See sa_disk_free_space system procedure.

Pre-defined dbspaces

SQL Anywhere uses the following pre-defined dbspaces for its databases:

Dbspace	Name
Main database file	system
Temporary file	temporary or temp
Transaction log file	translog
Transaction log mirror	translogmirror

You cannot create user-defined dbspaces with these names and you cannot drop the pre-defined dbspaces.

If you upgrade a version 10.0.0 or earlier database with user-defined dbspaces that use the pre-defined dbspace names, then all references to these dbspaces in SQL statements are assumed to be referring to the user-defined dbspaces, and not the pre-defined dbspaces. The only way that you can refer to the pre-defined dbspaces is by dropping the user-defined dbspaces, or renaming them to not use the same names as the pre-defined dbspaces.

The ALTER DBSPACE statement supports the pre-defined dbspace names so you can add more space to them. See ALTER DBSPACE statement.

The DB_EXTENDED_PROPERTY function also accepts the pre-defined dbspace names. See DB_EXTENDED_PROPERTY function [System].

Additional files

Other files can also become part of a database system, including:

Dbspace files You can spread your data over several separate files, in addition to the database file. See CREATE DBSPACE statement.
For information on dbspaces, see Using additional dbspaces.
Transaction log mirror files For additional security, you can create a mirror copy of the transaction log. This file typically has the extension .mlg. See Protecting against media failure on the transaction log.

Send feedback about this page via email or DocCommentXchange