February 2010 Edition
How to Manage Temporary Files in SQL Anywhere 11
A tempfile (sqla####.tmp) is a file created by SQL Anywhere server on startup and deleted on shutdown. As its name suggests, it is used to hold temporary information while the database server is running. It does not hold information that needs to be kept between sessions. It is used by the server for operations like sorting|distinct|unions when there is not enough cache available.
The location of the file is specified by one of the TMP,TMPDIR or TEMP environment variables or by '-dt' server option. (e.g. dbeng11 -dt "." - creates a temporary file in the current directory ). The location might be important for performance consideration. Similarly to .db and transaction log file, if this file is heavily used by the server (e.g. queries with lots of sorts/unions), for better IO concurrency, it is recommended to place it on a separate physical drive.
Unlike other DBMS systems (e.g. ASE, SQL Server) in SQL Anywhere there is no such thing as a temporary database (tempdb). Temp objects are created and stored on tempfile, but they don’t need to be explicitly accessed via a “tempdb” prefix.
In the event of system crash or server being killed, a tempfile will remain until is deleted manually or until another server starts up at which time it deletes any remaining tempfiles.
SQL tips related to tempfile:
To find out:
the actual file name :
the current size of tempfile in bytes:
Possible issues with temporary files
It is not uncommon for inexperienced SQL developer to create a request that will quickly cause server to allocate large amounts of temporary space. When in many cases this might be necessary in order to obtain required results, quite often a user may create a so called 'run-away query' causing the file system to fill up. This usually creates all sorts of problems e.g. system hang/system failure.
A simple example involves a SELECT statement from 3 tables with a missing JOIN condition. This shows how easy it is to create a SQL statement that will not only run for minutes/hours, but also use gigabytes of tempfile space.
SELECT top 1 *
It is important for system administrators to know that once a temporary file has grown there is no way, other than restarting the server, to automatically shrink it. Therefore, it is recommended for a dba to implement some measures that would prevent such incidents from happening.
How to prevent an unexpected growth
SQL Anywhere provides 2 server options which control space usage for a tempfile. "TEMP_SPACE_LIMIT_CHECK" option causes server to respect space limit and, 'MAX_TEMP_SPACE' allows setting the actual space limit in terms of size.
A request, which will cause temporary file to grow over 'MAX_TEMP_SPACE' limit, will stop and generate the following error:
There was an error reading the results of the SQL statement.
a failure with such error should be taken care of within an application.
Additionally to setting 'hardcoded' limits on the tempfile, which might be machine specific, it is recommended to setup a monitoring tool that would notify administrator about possible issues, so then he can analyze it and take preventive steps in order to stop the file system from being filled up and subsequently cause system failure.
Monitoring tempfile space usage
Additionally to using above server options, it is good practice to a have a way of monitoring the database server, and therefore act proactively on possible space issues.
One of the features of SQL Anywhere are system events. System events are simply events that are triggered by the server when a certain condition is true. For more information see http://dcx.sybase.com/index.php#1100en/dbadmin_en11/events-events.html.
SQL Anywhere provides different types of events, and one of them is 'TempDiskSpace' event.
Here is an example of an event that will log a message to server's console log every time the amount of available free space for a temporary file is less than 50%.
CREATE EVENT "evt_monitor_temp_space" TYPE "TempDiskSpace"
IF event_parameter( 'NumActive' ) <= 1 THEN
Note: that this can be modified to execute any SQL statement or use an external call (e.g. 'xp_cmdshell') to send an email etc.
Here is an example of messages generated by this event:
0% (214 Mb). of free space (22165 Mb) have been used by C:\DOCUME~1\lchmura\LOCALS~1\Temp\asat0001.tmp
How to identify a 'runaway' request
SQL Anywhere provides a set of dynamic properties, per database and per connection which is useful in identifying a runaway request. Analyzing these property values over time gives a good indication of tempfile usage per connection, and helps to quickly isolate the ‘runaway’ connection.
Below is an example of a SQL statement which will create (if it doesn’t exist) the sa_tempfile_monitor table, and store database and connection properties. (Note, the same statement could be added to the event above, so instead of being run manually, it could run automatically whenever the amount of free space on disk is less than 50%).
First make sure -zl and -zp options are turned on the server. This will cause server to capture the most recent prepared SQL statement. This can be done on server startup command line (dbsrv11 ... -zl -zp .. ) or from dbisql:
In order to collect necessary information about run-away process, from dbisql run:
if not exists (select * from sysobjects where name like 'sa_tempfile_monitor')
'TempTablePages','TempFilePages' - reports tempfile usage per connection (in pages)
Once data is collected, with a number of simple SQL statements the dba should be able to find out which connection was causing tempfile growth and what was executing at the time.
Here are some examples:
% usage of tempfile per connection over time:
where 'Number' represents connection ID, and UserID login id.
Once a connection causing the problem has been identified, by running :
select * from sa_tempfile_monitor where Number = 2
where '2' is the connection id, the dba should be able to find out what SQL statement has been executed, and then work on fixing it.
Here is a quick example
Start demo11 database:
"C:\Program Files\SQL Anywhere 11\Bin32\dbsrv11.exe" -zl -zp -m -c 50P -n demo11 "C:\Documents and Settings\All Users\Documents\SQL Anywhere 11\Samples\demo.db"
Open 2 dbisql windows (C:\>"%sqlany11%\bin32\dbisql.exe" -c uid=dba;pwd=sql;eng=demo11).
In first dbisql simulate 'run-away' request by running:
SELECT top 1 *
From the second dbisql window, run the following :
select * From sa_conn_properties() where PropName like 'TempTablePages'
this will find out the request's 'Connection ID' which uses the most of TempTablePages.
select * From sa_conn_info() where Number =
select * from sa_conn_properties() where Number =
will give more information about what this connection is actually doing.
Having this information a developer/administrator should be able to isolate which portion of the code triggers a run-away process, and fix it.