Max_temp_space still problematic. Database Asserts

[former_SQLA_member1694871]

Environment - 17.0.11.7058 running on Windows Server Page Size - 8k (8192)

I'm working with a team that has a fairly extensive SQL Anywhere implementation that they are using in conjunction with Mobilink.

I started looking into this issue, but it looked like the version my group is running

link max temp space issues

Their database is running along around the 76GB in size range. Multiple stores have smaller SQL Anywhere installations and are sending Mobilink messages to the master database on the hour.

Sometime back they started running into an issue with their temp file growing to the point where the hard disk would run out of hard disk space and the database would assert. They found that they have needed to shut the database down every 3 to 4 days to allow the temp file to reset so the database doesn't cause the machine to go down.

image large temp file

If the image does not link correctly, the size of the temp file "sqla0000.tmp" is 136GB in this picture.

The problem we have is the max_temp_space option is set to 100g

Some additional parameters that might help... max_temp_space 100g

temp_space_limit_check - On

ExtendedTempWrite - 22954912

FreePages - 1707

tempFileName - T:\\SQLANY\\sqla0000.tmp

TempTablePages 11284787

I had read in the link provided that the individual that ran into this issue back in 2017 had found out they could use the -ca switch to complement the temp file size issue that they were running into.

Is there a possibility that the -ca switch needs to be used with it?

Is there a possibility they might be running into a bug that has crept up again within SQL Anywhere itself?

Any suggestions on what might be needed would be greatly appreciated. Please advise if there is additional information I can send that might be able to help out with this issue.

Jeff Gibson

Exonero Solutions

Nashville, TN

[chris_keating]

The max_temp_space option defines per connection the limit on temporary usage. Your current setting indicates that a connection can use up to 100G before the error SQLSTATE_TEMP_SPACE_LIMIT is triggered. The TempTablePages value indicates there is a 86 TB temporary file ( TempTablePages * PageSize ). I prefer to query TempTablePages as the file size may not be correctly reported while the engine is running.

Is it possible that there is a very large Snapshot isolation query? The version_entries reported in sa_transactions() is a useful datapoint to determine the impact of queries using Snapshot isolation. MobiLink uses snapshots for downloads - if the download is large, the temp file is likely to grow to back the row versions on a busy server.

I would either manually query sa_performance_statistics() and sa_transactions() or create an event that periodically collects the results and stores in a table or file.

It would be also useful to enable RememberLastStatement so that you can query the what statement was being run by the connection that is consuming the temp space. To view the last statement, query CONNECTION_PROPERTY( 'LastStatement', <connnumber> ) or sa_conn_properties() to see all connections.

[johnsmirnios]

max_temp_space enforces a limit per connection. 2 connections with a 100GB limit could easily, in aggregate, use 136 GB of temp space.

When you report the TempTablePages value above, was that at the database level or the connection level? ie, are you using db_property('tempfilepages') or connection_property('tempfilepages')?

-ca 0 probably didn't have much to do with the old case that you referenced. The old case was more about certain code paths that didn't check the limit.

[VolkerBarth]

Just to add:

The system events GrowTemp and/or TempDiskSpace might be used here to get notified and possibly take action when the temp file grows unexpectedly.

[former_SQLA_member1694871]

That's good info to have. Thank you Volker!