Hi all, we are using SQL-Anywhere 10 and we have very often problems with unsufficient disk space (regarding temp files). I usually go under: 'C:\Users\USER_NAME\AppData\Local\Temp' and delete all files there.

My question is, how can I find the path for the temporary files of DB under another systems (rather than Windows 10)?

I tried the statement select db_property ('tempfilename'), but I think it doesnt always tell the truth!

Is there another way to know where are the temporary files saved? And may I delete them always even if DB is running?

Another question, how can I tell DB to use another path for his temporary files?

asked 26 Nov, 04:52

Sako's gravatar image

Sako
597314064
accept rate: 27%

edited 26 Nov, 06:44

Volker%20Barth's gravatar image

Volker Barth
34.7k337491732

2

In addition to what Volker said, perhaps you should investigate WHY so much temporary space is required. Perhaps you have some runaway queries; e.g., cross joins caused by missing predicates in the WHERE clause. Foxhound displays temp space usage at the database AND connection level.

(26 Nov, 14:12) Breck Carter

See here for the location of the temp file in SQL Anywhere 10:

Place different files on different devices

I'm not aware that the db_property ('TempFileName') value is unreliable, what results do you get? And of course it should work on any support OS.

Basically, you can set the SATMP environment variable to specify its location. AFAIK, you won't be able to change the tempfile location for a running database, as the temp file is locked by the engine (like the database and translog files are). So it will change its location once you have modified the SATMP variable and stopped and restarted the database.

permanent link

answered 26 Nov, 06:51

Volker%20Barth's gravatar image

Volker Barth
34.7k337491732
accept rate: 33%

Yes, thank you. It has worked, I changed the environment variable and it took effect after new starting the DB.

Now I understand the problem why the db_property ('TempFileName') was not always delivering the same path, since the TEMP environment variable could vary from system to other.

We usually dont use -dt option by starting DB at all.

(26 Nov, 08:16) Sako
2

I've tended to favour the -dt approach as it's easy to check that the service has been setup correctly, without having to wander around the Windows interface to find where environment variables are set these days!

(03 Dec, 07:39) Justin Willey
Replies hidden
1

> favour the -dt approach

Me too.

For the record, the docs are somewhat misleading. The dbsrv17 -dt option is a server-level option, whereas the temporary file is a database-level object. In other words, a temporary file is created for each database, and it is deleted when that database is stopped, which is not exactly the same thing as "when the server is stopped". This only matters when a single server runs multiple databases.

...but if you're running multiple databases in one server, your life is probably VERY interesting, and arcana like "all the temporary files are in one folder" might be important :)

(03 Dec, 10:34) Breck Carter
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:

×119
×13

question asked: 26 Nov, 04:52

question was seen: 87 times

last updated: 03 Dec, 10:40