Temporary files under Windows

[Baron]

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?

[Breck_Carter]

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.

[VolkerBarth · Accepted solution]

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.

[Baron · Accepted solution]

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.

[justin_willey · Accepted solution]

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!

[Breck_Carter · Accepted solution]

> 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 🙂