Temp-file growth

[bjanker77]

Hi.

One of our customers has a SQL Anywhere 12 (12.1.4142) database, about 24 Gb in size. This morning, the tmp-file is 34 Gb. We're wondering about the reason why this tmp-file grows so much? We are using #-tables and some heavy queries against the database, but for the most part it's just normal usage from a Powerbuilder 12.5 application. The service is started with these parameters:

-n maritech -x tcpip -gn 40 -ca 0 -ch 14000M -cl 14000M -c 14000M

The server has 24 Gb of RAM.

Is there anything we can adjust in the parameters, or is it normal that the tmp-file grows to that size?

Regards,

Bjarne Anker Maritech Systems AS Norway

[VolkerBarth]

How big is the temporary file usually?

You can certainly restrict the growth of the temporary file via the max_temp_space and temp_space_limit_check options. That may give a hint at which "heavy query" may have increased the file size.

I'd suggest to have a look at other FAQs related to temp files...

[bjanker77]

For some reason, our customer has chosen to include these parameters to the startup: -ca 0 -cl 14000M -ch 14000M -c 14000M

Which in short means that the service is started with 14Gb cache size, cache resizing disabled and no growing in the cache. Can that have negative consequences for the temo-file growth?

Regards,

Bjarne

[Breck_Carter]

In addition to what Volker and Martin say, one of the easiest ways to find runaway queries is to run Foxhound. This example shows a runaway connection using all the CPU time, but in your case it would show which connection used a lot of temporary space.

The decision to fix the RAM cache size is sometimes made to guarantee adequate resources for the database server, rather than let the server spend time dynamically adjusting the cache size, when the database server is the most important (or only important) process on the computer.

Also note: the physical temporary file will not shrink in size when the temporary space requirements shrink... until the database is restarted (which is when the temporary file is created).

If the temporary file is heavily used, on purpose, it might be a good idea to defragment it after it grows in size.

[MCMartin]

You will have to check the plans of the queries you execute. One common thing which grows the temp file is a query plan including a temporary table, they all go into the temp file. So probably you will find a cross join of tables in your plans which is leading to the observed effect.