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

asked 14 Jun '23, 17:06

Jeff%20Gibson's gravatar image

Jeff Gibson
1.8k406172
accept rate: 20%


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.

permanent link

answered 14 Jun '23, 21:46

John%20Smirnios's gravatar image

John Smirnios
11.9k396164
accept rate: 38%

edited 14 Jun '23, 21:47

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.

permanent link

answered 14 Jun '23, 22:31

Chris%20Keating's gravatar image

Chris Keating
7.7k49127
accept rate: 32%

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.

permanent link

answered 15 Jun '23, 03:59

Volker%20Barth's gravatar image

Volker Barth
40.0k361549819
accept rate: 34%

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

(15 Jun '23, 14:50) Jeff Gibson
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:

×240
×14

question asked: 14 Jun '23, 17:06

question was seen: 279 times

last updated: 15 Jun '23, 14:50