How do I control temporary file growth in SQL Anywhere?

Solution 1:

It is unlikely that either -ti 0 or -tl 0 have anything to do with the temp space problem in SQL Anywhere. It is most likely the result of a runaway query. If you are using version 9 you can turn on limit checking as follows:

SET OPTION PUBLIC.temp_space_limit_check = 'ON';

In versions 10 and 11 that option should already be on, but maybe it got turned off. The new max_temp_space option is also useful.

In earlier versions, you'll just have to find the runaway queries; Foxhound may help: http://www.risingroad.com/foxhound/index.html

See also "Danger! The Queries are Stampeding!" at http://sqlanywhere.blogspot.com/2009/03/danger-queries-are-stampeding.html

FYI the -ti 0 unlimited inactivity timeout setting is very common when you expect long periods of inactivity; e.g., overnight on a big web-based connection pool.

However, the -tl 0 unlimited liveness timeout setting is actually dangerous if it results in lots of zombie connections piling up (the clients are long dead but the server holds the connections open). Like the Help says, it's usually better to increase the timeout period if you are having premature liveness timeout problems; e.g., -tl 3600 for one hour.

AFAIK the statement "The connection will not be reset unless the client can not be reached via tcpip" appears to be an over-simplification: the checking of liveness packets seems to be rather more complex than a simple "cannot be reached" test.

Breck

Solution 2:

You're right that the -ti and -tl switches are unrelated to temp space.

Regarding the liveness issue, both the client and server send liveness packets every n/3 seconds, where n is the liveness timeout value. This only happens if no other packets have been sent in the meantime. If either side has not receive any packets from the other side after n seconds, the connection is dropped.

Liveness is not necessary in the case where the connection is actually dropped by the other side (eg. the application/server crashes or the machine reboots) since the TCP connection will be closed immediately. Liveness is useful, however, for detecting applications that are hung and network problems that prevent packets from getting through but do not cause the TCP connection to be dropped.