The three asterisks "***" show which Express Bug Fixes (EBFs) and Beta builds have appeared on the website since the previous version of this page.

Only EBFs for the latest fully-supported versions of SQL Anywhere (11.0.1 and 12.0.1) are shown here, plus Beta builds for 16.0.0.

Just because an older version or different platform isn't "fully supported" any more doesn't mean you can't download files (or ask questions, or get help), it just means there won't be any more new EBFs released.

Sunday, February 24, 2013

A temporary file, also known as a temporary dbspace, is automatically created whenever a SQL Anywhere database is started, and it is automatically deleted whenever that database is stopped. The files are named sqla0000.tmp, sqla0001.tmp and so on, and by default (on Windows 7) they are located in the C:\Users\[user-name]\AppData\Local\Temp folder:

The temporary file is used for temporary data. Exactly what constitutes "temporary data" is more or less a mystery, as are the conditions under which temporary data is written to the temporary file rather than stored in the database cache. You have control over the temporary file location, but the SQL Anywhere server has complete control over how, why and when the temporary file is used.

In terms of data nothing of lasting value is ever stored in these files. If for some reason (say, after a crash) one of these files remains in existence, it's just wasting space and you can go ahead and delete it. In case of doubt, delete: if it's still in use by SQL Anywhere you won't be able to.

In terms of performance, the temporary file is usually no problem. In fact, at most SQL Anywhere shops the temporary file is completely invisible: no one even knows it's there.

It is possible, however, for runaway temporary file usage to cause grief:

If SQL Anywhere decides to write the temporary data to the file instead of the database cache, excessive disk I/O may be the result.

If the temporary file is located on the same physical drive as other database files, the disk I/O may not be efficient.

Prior to SQL Anywhere 10, a runaway process could cause the server to crash because the drive holding the temporary file ran out of space. With version 10 and later, a runaway process is prevented from using more than 80% of the remaining disk space, which on today's enormous disk drives means...

...if not a crash, then at least "Holy Cow!" moment when first discovering that sqla0000.tmp has grown to 500G for a database that is only 300M in size.

When the max_temp_space limit kicked in, SQL Anywhere stopped the SELECT INTO and started rolling it back. The connection-level TempFilePages value started to sink, but the temporary file size remained at the "high water mark":

Temporary FileSize,TempFilePages
'526.97M','302.60M'

Eventually, the failing connection received a SQLCODE -1000 error message, but not until the connection-level TempFilePages had sunk back to pre-SELECT INTO levels:

Here's what the total temporary space usage looked like from Foxhound's point of view:

At first (bottom line), before the runaway SELECT INTO started executing, the total "Temp Space" amount was 760K.

In less than a minute (6th line up from the bottom), the amount reached 503M.

At this point, the "adhoc-queries" connection (see the lower frame in the image below) was responsible for 502M of the total:

The next sample (7th line up from the bottom of the first image) shows the total amount has started to drop (463M). At this point SQL Anywhere has cancelled the SELECT INTO operation and started to roll it back.

Two minutes later (2nd line from the top), SQL Anywhere has finished cancelling the SELECT INTO.

The total Temp Space is back down to 868K, and at 3:50:18 the SQLCODE -1000 error was returned to the "adhoc-queries" connection:

"Why bother setting a limit, just increase the database cache."

Here's why: In the test above, the dbsrv12 -c 1G -ch 2G options were used even though the entire database file was only 288M. The database was completely idle except for the runaway SELECT INTO test, yet SQL Anywhere chose to grow the temporary file to 527M instead of using (or growing) the cache.

Hence the earlier comment about the conditions under which temporary data is written to the temporary file being "more or less a mystery".

As with other kinds of AUTOINCREMENT columns, the @@IDENTITY connection-level variable would return the most recent value calculated across all columns; i.e, in the example above, @@IDENTITY would contain the successive values 1, 1, 2, 2, 1, 2 after each of the six INSERT statements.

The three asterisks "***" show which Express Bug Fixes (EBFs) and Beta builds have appeared on the website since the previous version of this page.

Only EBFs for the latest fully-supported versions of SQL Anywhere (11.0.1 and 12.0.1) are shown here, plus Beta builds for 16.0.0.

Just because an older version or different platform isn't "fully supported" any more doesn't mean you can't download files (or ask questions, or get help), it just means there won't be any more new EBFs released.