|
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 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 |
|
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. |
|
The Is it possible that there is a very large Snapshot isolation query? The version_entries reported in I would either manually query 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 |
|
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. That's good info to have. Thank you Volker!
(15 Jun '23, 14:50)
Jeff Gibson
|
