Checks for catalog store temporary space on a per connection basis.
ON, OFF (no limit checking occurs)
ON
Can be set only for the PUBLIC group. DBA authority required.
When TEMP_SPACE_LIMIT_CHECK is ON, the database server checks the amount of catalog store temporary file space that a connection uses. If a connection requests more than its quota of temporary file space when this option is set to OFF, a fatal error can occur. When this option is set to ON, if a connection requests more than its quota of temporary file space, the request fails and the error “Temporary space limit exceeded” is returned.
Two factors are used to determine the temporary file quota for a connection: the maximum size of the temporary file, and the number of active database connections. The maximum size of the temporary file is the sum of the current size of the file and the amount of disk space available on the partition containing the file. When limit checking is turned on, the server checks a connection for exceeding its quota when the temporary file has grown to 80% or more of its maximum size, and the connection requests more temporary file space. Once this happens, any connection fails that uses more than the maximum temporary file space divided by the number of active connections.
This option is unrelated to IQ temporary store space.
To constrain the growth of IQ temporary space, see “QUERY_TEMP_SPACE_LIMIT option” and “MAX_TEMP_SPACE_PER_CONNECTION option”.
A database is started with the temporary file on a drive with 100MB free and no other active files on the same drive. The available temporary file space is thus 100MB. The DBA issues:
SET OPTION PUBLIC.TEMP_SPACE_LIMIT_CHECK = 'ON'
As long as the temporary file stays below 80MB, the server behaves as it did before. Once the file reaches 80MB, the new behavior might occur. Assume that with 10 queries running, the temporary file needs to grow. When the server finds that one query is using more than 8MB of temporary file space, that query fails.
You can obtain information about the space available for the temporary file using the sa_disk_free_space system procedure. For more information, see “sa_disk_free_space system procedure” in SQL Anywhere Server – SQL Reference > System Objects > System procedures > Alphabetical list of system procedures.
