Taming A Runaway Temporary File

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:

 Directory of C:\Users\Breck\AppData\Local\Temp

02/23/2013  10:04 AM        26,353,664 sqla0000.tmp
02/23/2013  10:52 AM           200,704 sqla0001.tmp
02/23/2013  10:55 AM           102,400 sqla0003.tmp

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.
  

It's easy to tell SQL Anywhere where to put the temporary file: just use the dbsrv12 -dt option or one of the SATMP, TMP, TMPDIR or TEMP environment variables.

The next step, telling SQL Anywhere to stop runaway connections from using too much temporary space, is harder. Here's how...

Step 1: Put a limit on temporary space usage

Pick a limit, say 512M, on the amount of temporary storage used by each individual connection, and put that limit into effect:

SET OPTION PUBLIC.temp_space_limit_check = 'On';

SET OPTION PUBLIC.max_temp_space = '512M';

Double-check that these settings apply to end-user connections. One method is to have the end user connect via dbisql, run the SET command (no parameters, just "SET") and report the values displayed:

Step 2: Test the limit check

Here's command to start a connection named "adhoc-queries" that will be used to test the limit check:

"%SQLANY12%\bin32\dbisql.com"^
  -c "ENG=inventory_envy;DBN=inventory;UID=k.delacruz;PWD=sql;CON=adhoc-queries"

Here's a query that displays the size of the temporary file, plus the temporary space currently used by "adhoc-queries"; this query should be run on some OTHER connection:

SELECT STRING (
          CAST ( CAST ( DB_EXTENDED_PROPERTY ( 'FileSize', 'temporary' ) AS INTEGER )
             * 4096.0 / ( 1024 * 1024 )
             AS DECIMAL ( 11, 2 ) ),
          'M' ) 
          AS "Temporary FileSize",
       STRING ( 
          CAST ( CAST ( CONNECTION_PROPERTY ( 'TempfilePages', Number ) AS INTEGER )
             * 4096.0 / ( 1024 * 1024 )
              AS DECIMAL ( 11, 2 ) ),
          'M' ) 
          AS "TempFilePages"
  FROM sa_conn_properties() 
 WHERE PropName = 'Name' 
   AND Value = 'adhoc-queries'

Temporary FileSize,TempFilePages
'1.04M','.11M'

So far so good... but here's a query to run on the "adhoc-queries" connection that will cause the temporary space usage to soar:

SELECT a.*
  INTO #temp_inventory
  FROM inventory AS a,
       inventory AS b;

It's self-join between a million-row table and itself, and because the WHERE clause has been omitted (a common error with adhoc queries) it is effectively a CROSS JOIN.

In testing that query took less than a minute to reach the max_temp_space limit:

Temporary FileSize,TempFilePages
'41.04M','35.90M'

Temporary FileSize,TempFilePages
'462.97M','461.87M'

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:

Could not execute statement.
Temporary space limit exceeded
SQLCODE=-1000, ODBC 3 State="HY000"

Temporary FileSize,TempFilePages
'526.97M','.23M'

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".

---