Skip to end of metadata
Go to start of metadata

Purpose

The purpose of this page is to provide information about working with SQL Anywhere temporary files.

Overview

A temporary file (sqla####.tmp) is a file that is created by a SQL Anywhere database 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 database server for operations such as sorting, distinct, and unions when there is not enough cache available.

The location of the temporary file is specified by the TMP, TMPDIR, or TEMP environment variable or by the -dt database server option. For example, dbeng16 -dt "." creates a temporary file in the current directory. The temporary file's location can be a performance consideration. If the temporary file is used significantly by the database server, it is recommended that you place the temporary file on a separate physical drive to improve I/O concurrency.

Unlike other DBMS systems (such as ASE or SQL Server), SQL Anywhere, does not have a temporary database (tempdb). Temporary objects are created and stored in the temporary file, but they do not need to be explicitly accessed via a tempdb prefix.

In the event of a system crash or a databse server stopping unexpectedly, a temporary file persists until it is deleted manually or until another database server starts up, at which time the database server deletes any other existing temporary files.

SQL Tips Related to Temporary Files

  • Find out which directory the temporary file was created in:
         SELECT PROPERTY ('TempDir');
  • Determine the temporary file name:
         SELECT DB_PROPERTY('TempFileName');
  • Obtain the current size of the temporary file in bytes:
         SELECT DB_EXTENDED_PROPERTY ('FileSize', 'temporary') * PROPERTY ('PageSize');

Potential Issues with Temporary Files

It is possible to create a request that quickly causes the database server to allocate large amounts of temporary space. While in many cases this might not be necessary to obtain the required results, users can also create runaway queries that fill up the file system. These types of queries can create numerous problems, including a system hang or failure.

A simple example involves a SELECT statement from three tables with a missing JOIN condition. The following example demonstrates a SQL statement that can run for minutes or hours and consume several gigabytes of temporary file space.

SELECT top 1 *
     FROM SYSDEPENDENCY
          ,SYSOBJECT
          ,SYSPROCPARM
ORDER BY 1, 2, 3, 4, 5

Once a temporary file has grown, there is no way to reduce its size other than restarting the database server. Therefore, it is recommended that you implement measures to prevent such incidents from happening.

How to Prevent Unexpected Growth

SQL Anywhere provides two database options that control space usage for temporary files. The TEMP_SPACE_LIMIT_CHECK option forces the database server to respect the space limit and the MAX_TEMP_SPACE option allows you to set the space limit in terms of size. For example:

SET OPTION PUBLIC.TEMP_SPACE_LIMIT_CHECK = 'On';
SET OPTION PUBLIC.MAX_TEMP_SPACE = '1G'; -- sets the limit to 1GB

A request that causes the temporary file to grow over the limit specified by MAX_TEMP_SPACE is stopped and the following error is generated:

There was an error reading the results of the SQL statement.
The displayed results may be incorrect or incomplete.
Temporary space limit exceeded.
SQLCODE=-1000, ODBC 3 State="HY000"

Applications should be designed to handle this type of error.

To set hard coded limits on the temporary file, which might be machine specific, it is recommended that you set up a monitoring tool to notify the administrator about possible issues, so that they can analyze the status and take steps to stop the file system from filling up, and subsequently causing a system failure.

Monitoring Temporary File Space Usage

In addition to using database options to manage the temporary file, you should have a way to monitor the database server so that you can proactively manage potential space issues.

SQL Anywhere supports system events, which are events that are triggered by the database server when a certain condition is true. For more information, see the Related Documents section.

You can use the TempDiskSpace to obtain the amount of free disk space on the device holding the temporary file. The following example uses the TempDiskSpace event and sends a message to the database server message log each time the amount of available free space for a temporary file is less than 50%.

CREATE EVENT "evt_monitor_temp_space" TYPE "TempDiskspace"
WHERE EVENT_CONDITION ('TempFreePercent') < 50
HANDLER
BEGIN
-- this event will write a message to console log when there is less
-- than 50% of free space on the device where tempfile is located
declare tf varchar(128);
declare free varchar(128);
declare crn varchar(128);
declare prc varchar(3);

     IF event_parameter('NumActive') <= 1 THEN
          -- find out temp file name
          select convert(varchar(128),(convert(bigint, DB_EXTENDED_PROPERTY('FileSize',
               'temporary')) * convert(bigint, PROPERTY('PageSize')) / 1024 / 1024))
          into crn from dummy;
          -- find out number of available free space
          select convert(varchar(128), convert(int, free_space / 1024 / 1024)) into
               free from sa_disk_free_space() where dbspace_name = 'temporary';
          -- calculate % of free space
          if free <> 0 then select
               convert(varchar(4), convert(integer, convert(float, crn) /
               convert(float, free) * 100)) + '%' into prc from dummy end if;
          -- write space information to console log
          MESSAGE prc + '(' + crn + ' Mb). of free space (' + free + ' Mb)' + '
               have been used by' + tf;
      END IF;
END;

This even can be modified to execute any SQL statement or to use an external call, such as xp_cmdshell, to send an e-mail or perform another action.

Here is an example of the messages generated by this event:

0% (214 Mb). of free space (22165 Mb) have been used by C:\DOCUME~1\user_name\LOCALS~1\Temp\asat0001.tmp
2% (558 Mb). of free space (21820 Mb) have been used by C:\DOCUME~1\user_name\LOCALS~1\Temp\asat0001.tmp
Cache size adjusted to 1362112K
4% (974 Mb). of free space (21404 Mb) have been used by C:\DOCUME~1\user_name\LOCALS~1\Temp\asat0001.tmp
4% (1038 Mb). of free space (21340 Mb) have been used by C:\DOCUME~1\user_name\LOCALS~1\Temp\asat0001.tmp

How to Identify a Runaway Request

SQL Anywhere provides a set of dynamic properties, per database and per connection, that can be used to identify a runaway request. Analyzing these property values over time gives a good indication of temporary file usage per connection, and can help you quickly identify runaway connections.

Below is a sample SQL statement that creates the sa_tempfile_monitor table if it doesn't already exist, and stores database and connection properties. The same statement could be added to the event above, so that instead of being run manually, it could run automatically whenever the amount of free space on disk is less than 50%.

You must start the database server with the -zl and -zp options to use this example. These options cause the database server to caputre the most recently prepared SQL statement. You can specify these options when starting the database server (for example dbsrv16 ... -zl -zp ...) or you can use the following SQL statements to enable these options for a database server that is already running:

CALL sa_server_option('RememberLastStatement', 'ON');
CALL sa_server_option('RememberLastPlan', 'ON');

To collect the necessary information about the runaway process, run the following SQL statement:

if not exists (select * from sysobjects where name like 'sa_tempfile_monitor')
     select getdate() as d, db_property('ExtendTempWrite') as ExtendTempWrite,
     db_property('TempTablePages') as TempTablePages, prop.Number,
     prop.PropNum, prop.PropName, prop.Value,
     inf.Name, inf.Userid, inf.LastReqTime, inf.BlockedOn
     into sa_tempfile_monitor
     from sa_conn_properties() prop, sa_conn_info() inf
     where prop.Number = inf.Number and
     prop.PropName in
     ('TempTablePages', 'TempFilePages', 'LastStatement', 'LastPlanText', 'CurrentProcedure', 'CurrentLineNumber')
else
     insert into sa_tempfile_monitor
     select
     getdate(), db_property('ExtendTempWrite'), db_property('TempTablePages'),
     prop.Number, prop.PropNum, prop.PropName, prop.Value,
     inf.Name, inf.Userid, inf.LastReqTime, inf.BlockedOn
     from sa_conn_properties() prop, sa_conn_info() inf
     where prop.Number = inf.Number and prop.PropName in
     ('TempTablePages', 'TempFilePages', 'LastStatement', 'LastPlanText', 'CurrentProcedure', 'CurrentLineNumber')

  • 'TempTablePages', 'TempFilePages' - reports temporary file usage per connection (in pages)
  • 'LastStatement', 'LastPlanText', 'CurrentProcedure', 'CurrentLinenumber' - helps identify which SQL request is running at the time

Once the data has been collected, you should be able to find out which connection was causing the temporary file to grow and what was executing at the time by running queries. For example:

-- Percentage use of the temporary file per connection over time
select  d, ExtendTempWrite, TempTablePages, Number, UserID,
     convert(Integer, convert(Bigint, Value) / TempTablePages * 100) PerUsage
from sa_tempfile_monitor
where PropName = 'TempTablePages'
order by d, number

Number represents the connection ID and UserID is the database user.

Once the connection causing the problem has been identified, you can run the following statement, where 2 is the connection ID, so that you can determine which SQL statement was executed.

SELECT * FROM sa_tempfile_monitor WHERE Number = 2;

Once you know which statement is causing the problem, you can work on fixing it.

Example

  1. Using command prompt, run the following command to start the sample database:
         "C:\Program Files\SQL Anywhere 16\Bin64\dbsrv16.exe" -zl -zp -m -c 50P
         -n demo16 "C:\Documents and settings\All Users\Documents\SQL Anywhere 16\Samples\demo.db"
  2. Open two Interactive SQL windows by running the following command twice:
         "C:\Program Files\SQL Anywhere 16\Bin64\dbisql.exe" -c "uid=DBA;pwd=sql;eng=demo16"
  3. In the first Interactive SQL window, run the following query to simulate a runaway request:
         SELECT top 1 *
              FROM SYSDEPENDENCY,
                   SYSOBJECT
                   SYSPROCPARM
              ORDER BY 1, 2, 3, 4, 5;
  4. From the second Interactive SQL window, run the following statement:
         SELECT * FROM sa_conn_properties() WHERE PropName LIKE 'TempTablePages'
         ORDER BY CONVERT(integer,value) DESC
    This query returns the connection ID of the request that is using the most temporary table pages.
  5. Run the following queries:
         SELECT * FROM sa_conn_info() WHERE Number =
              (SELECT TOP 1 Number FROM sa_conn_properties() WHERE PropName
              LIKE 'TempTablePages' ORDER BY CONVERT (integer, value) DESC)
         SELECT * FROM sa_conn_properties() WHERE Number =
              (SELECT TOP 1 Number FROM sa_conn_properties() WHERE PropName
              LIKE 'TempTablePages' ORDER BY CONVERT (integer, value) DESC)

    These statements provide more information about what this connection is doing. With this information, you should be able to isolate which portion of the code triggers a runaway process and then fix it.

Related Content

Related Documents

System events

-dt database server option

TEMP_SPACE_LIMIT CHECK option

MAX_TEMP_SPACE option

Related SAP Notes/KBAs

 

6 Comments

  1. I have corrected the SQL code, now you can copy&paste it.

  2. Hello experts,

    My DB has created an sqla0002.tmp file in the local disk C:,  the size  is 40GB and I need to remove it to recover the space again.

    The question is:  Is there any secure method to remove these files once the database has created them? Or simply I need to remove the file from OS?

     

    1. Former Member

      The name of that file does indicate it is a SQL Anywhere temporary database file.  The number 2 in the name would also indicate it
      belongs or belonged to a third database instance (as in the third database started on the same or another database server on
      that machine). If you don't normally have 3 databases started at one time on this same machine you can try deleting it.

      Temporary files should not persist if they are not in use. Normally these files should be cleaned up by the database server
      when it stops.  So the first thing to try would be to stop all running dbsrv12 and dbeng12 processes to see if the file is removed.

      If shutting down your database server(s) does not remove that file for you, you may have an orphaned file. The temporary file
      can become orphaned if the database server that created it was forced down without a chance to do it's normal clean up.
      If your temporary enviornment variables have changed (or SATMP) or you have used a different -dt setting then the server may
      not see the file to clean it up.  File system permissions can also sometimes hide files or prevent clean up. Fortunately this has
      become an exceeding rare occurrance from my experience.

      If that fle is in use by a running database server the file will be locked exclusively, so, any attempts to delete this file should be safe.

       

      1. Thanks Nick,

        Effectively, after restarting the SQL anywhere service (from windows services), the .tmp files were cleaned up by the data base.

        Regards

        DO

         

  3. Former Member

    Nick,

    I have noticed some large files (> 2GB) with the names like _T2, _T3 etc in the temp folder. These are in addition to the sqla000n.tmp files.

    These files are about three weeks old and before I delete them, I just wanted to find out what these files contained and if it will require a server restart to delete them.

    Thanks,
    Bhikam 

    1. Hi Bhikam,

      are you sure these are SQL Anywhere files? Temporary files in the format of 'sqlaXXXX.tmp'  where XXXX- is 4 digit number e.g. sqla0004.tmp. You can isolate SQL Anywhere from all other process and create SATMP , or specifying a separate directory location for temporary files with '-dt' option.

      It's possible, in a case of server process getting killed or crash, that would not get cleaned up. In this case you are safe to remove it a new file is being created on startup up.

      Note, a better place to ask this type of questions would be our SAP Community ( http://go.sap.com/community/topic/sql-anywhere.html ) . You would need to registere first, then ask a question (click your profile - Ask Question : https://answers.sap.com/questions/ask.html ).

      In this case we would need to know SQLA version, operating system and directory listing.

      Regards,
      Lucjan