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

asked 12 Jan '15, 03:05

Bjarne%20Anker's gravatar image

Bjarne Anker
745323648
accept rate: 10%

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

(12 Jan '15, 03:31) Volker Barth

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

(12 Jan '15, 04:43) Bjarne Anker

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.

alt text

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.

permanent link

answered 12 Jan '15, 08:17

Breck%20Carter's gravatar image

Breck Carter
32.5k5417261050
accept rate: 20%

edited 12 Jan '15, 08:30

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.

permanent link

answered 12 Jan '15, 03:33

Martin's gravatar image

Martin
9.0k130169257
accept rate: 14%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×438
×14

question asked: 12 Jan '15, 03:05

question was seen: 2,638 times

last updated: 12 Jan '15, 08:30