We are currently experiencing a growing temporary file problem. Watching one of our sites we can see a growth of 100 – 200 MB a day at one site we are watching. At this site a failure occured when the temp file reached 20Gb and a free space issue occured.
We are currently suppressing timeouts. -ti and -tl are set to zero. What is the chance that temp tables are building up due to this configuration?
Additionally. To further my understanding of the -tl flag, is the following a true statement: The connection will not be reset unless the client can not be reached via tcpip.
Best Answer
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