Microsoft ODBC Driver 17 for SQL Server - Query Result TEMP Storage

Question

Microsoft ODBC Driver 17 for SQL Server - Query Result TEMP Storage

Derek Lauro 1

My question is simple. Is there a property of the Microsoft ODBC Driver 17 for SQL Server (or any version for that matter) to change where it stores the query results?

By default, this driver stores query result data into files under “%USERPROFILE%\AppData\Local\Temp". You can see this generating files as connections are opened and query results are gathered.

The problem is, due to a new behavior in Windows Server 2019, a user's %TEMP% folder is automatically deleted every 7 days. Not cleared out, deleted (and not recreated for some reason). Seems like an oversight from Microsoft, but here we are. This prevents the Microsoft ODBC Driver 17 for SQL Server from being able to provide query results to my application, and any application that relies on accessing %TEMP% no longer functions properly until the user account logs off and back on, or the server is restarted. This makes it very difficult to manage an application that needs to be running 24/7/365. In the article they provide a workaround to remove or delay this cleanup with a registry hack, but this would be difficult to roll out on a global scale for all of my clients.
Here is the article I am referencing: https://learn.microsoft.com/en-us/troubleshoot/windows-server/shell-experience/temp-folder-with-logon-session-id-deleted

So rather than relying on the unreliable Windows temp directory, I'm wondering if I can define my own query result temp directory, any ideas? I'm not seeing any properties of the ODBC to be able to do so.

David Browne - msft 3,851 Reputation points

2020-11-11T16:18:57.153+00:00

If you change this for the ODBC driver, you still might have other programs that fail. Why not use the workaround suggested by the article and exclude the %TEMP% folder from the cleanup process? https://learn.microsoft.com/en-us/troubleshoot/windows-server/shell-experience/temp-folder-with-logon-session-id-deleted#workaround-1-recommended-remove-temp-entry-from-folder-value
Derek Lauro 1 Reputation point

2020-11-11T16:23:59.77+00:00

Only reason I am hesitant about the registry workaround is trying to deploy that change for all of our installs. Going to be difficult to manage. Would potentially need to execute this across a few thousand servers over the lifetime of Server 2019+. If I could handle this on the ODBC setup side or from our application automatically it would be best.
Derek Lauro 1 Reputation point

2020-11-11T16:29:50.11+00:00

you still might have other programs that fail

I should also say that I don't care what happens to other programs. For this purpose I'm just trying to make sure my application stays running for the client. If the client decides they need to make registry changes to address issues they are having with other programs, that is on them to handle.
David Browne - msft 3,851 Reputation points

2020-11-11T17:02:57.427+00:00

Does changing the TEMP environment variable change the storage location for the files? If so you could set it on startup or from the program that launches your app.
Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator

2020-11-12T22:22:12.847+00:00

This is more of a silly question to satisfy my own curiousity than an attempt to help, but are these temp files something that ODBC produces by default? Or do you request them by some setting? And how are these temp files used? Is the application really going back to access temp files that are eight days old?
Derek Lauro 1 Reputation point

2020-11-12T22:28:45.6+00:00

This is how the ODBC driver itself works. My application is not reading these files specifically. It's just temp storage for the ODBC to cache query results. Once the TEMP folder goes away, the ODBC driver ceases to work for any application.
Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator

2020-11-12T22:38:52.923+00:00

I will have to admit that I've never heard about that. But it sounds a little scary to me. How does ODBC knows what is in the cache is still valid? The data in the database may have changed.

What is the actual issue you see in the application? Error messages? Performance dropping?
Derek Lauro 1 Reputation point

2020-11-13T14:13:53.173+00:00
It's not a long-term cache - it stores the query result and that is it - It's never used again after.
In the application, no results are returned. Here's how it looks:

Application sends query to ODBC Driver (ex. select * from table)

ODBC Driver sends query to SQL Server

SQL Server executes query

SQL Server returns 9,000 results to ODBC Driver

ODBC driver no longer has access to TEMP folder to record those results

ODBC driver returns 0 results to application.

In the end, the application basically can't get results from the database because the ODBC driver is not functioning properly.
Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator

2020-11-13T22:40:51.873+00:00

There is still something funny here. I don't see why ODBC would generate these files. I tried running a query through SQLCMD, which uses ODBC, and I could not see any files in %TEMP%.

But else - did you try David Browne's suggestion to change %TEMP% from your application?

3 answers

Your answer

David Browne - msft 3,851 Reputation points

2020-11-11T16:18:57.153+00:00

If you change this for the ODBC driver, you still might have other programs that fail. Why not use the workaround suggested by the article and exclude the %TEMP% folder from the cleanup process? https://learn.microsoft.com/en-us/troubleshoot/windows-server/shell-experience/temp-folder-with-logon-session-id-deleted#workaround-1-recommended-remove-temp-entry-from-folder-value
Derek Lauro 1 Reputation point

2020-11-11T16:23:59.77+00:00

Only reason I am hesitant about the registry workaround is trying to deploy that change for all of our installs. Going to be difficult to manage. Would potentially need to execute this across a few thousand servers over the lifetime of Server 2019+. If I could handle this on the ODBC setup side or from our application automatically it would be best.
Derek Lauro 1 Reputation point

2020-11-11T16:29:50.11+00:00

you still might have other programs that fail

I should also say that I don't care what happens to other programs. For this purpose I'm just trying to make sure my application stays running for the client. If the client decides they need to make registry changes to address issues they are having with other programs, that is on them to handle.
David Browne - msft 3,851 Reputation points

2020-11-11T17:02:57.427+00:00

Does changing the TEMP environment variable change the storage location for the files? If so you could set it on startup or from the program that launches your app.
Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator

2020-11-12T22:22:12.847+00:00

This is more of a silly question to satisfy my own curiousity than an attempt to help, but are these temp files something that ODBC produces by default? Or do you request them by some setting? And how are these temp files used? Is the application really going back to access temp files that are eight days old?
Derek Lauro 1 Reputation point

2020-11-12T22:28:45.6+00:00

This is how the ODBC driver itself works. My application is not reading these files specifically. It's just temp storage for the ODBC to cache query results. Once the TEMP folder goes away, the ODBC driver ceases to work for any application.
Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator

2020-11-12T22:38:52.923+00:00

I will have to admit that I've never heard about that. But it sounds a little scary to me. How does ODBC knows what is in the cache is still valid? The data in the database may have changed.

What is the actual issue you see in the application? Error messages? Performance dropping?
Derek Lauro 1 Reputation point

2020-11-13T14:13:53.173+00:00

It's not a long-term cache - it stores the query result and that is it - It's never used again after.
In the application, no results are returned. Here's how it looks:

Application sends query to ODBC Driver (ex. select * from table)

ODBC Driver sends query to SQL Server

SQL Server executes query

SQL Server returns 9,000 results to ODBC Driver

ODBC driver no longer has access to TEMP folder to record those results

ODBC driver returns 0 results to application.

In the end, the application basically can't get results from the database because the ODBC driver is not functioning properly.
Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator

2020-11-13T22:40:51.873+00:00

There is still something funny here. I don't see why ODBC would generate these files. I tried running a query through SQLCMD, which uses ODBC, and I could not see any files in %TEMP%.

But else - did you try David Browne's suggestion to change %TEMP% from your application?

Answer 1

m 4,276

Hi @Derek Lauro ,

Is there a property of the Microsoft ODBC Driver 17 for SQL Server (or any version for that matter) to change where it stores the query results?

Yes. Do you want to change the default stored path to a new specific one? You can choose the 'Tracing' and then click 'Browse' change the default path to what you want and then click OK.

So rather than relying on the unreliable Windows temp directory, I'm wondering if I can define my own query result temp directory, any ideas?

Same answer as the first question.

BR,
Mia

If the answer is helpful, please click "Accept Answer" and upvote it.

Derek Lauro 1 Reputation point

2020-11-12T15:19:10.733+00:00

Unfortunately, changing the Tracing Log Path location does not change where the ODBC stores its tmp files. After changing this value and restarting, you can see the tmp files are still generated in %USERPROFILE%\AppData\Local\Temp

Answer 2

m 4,276

Hi @Derek Lauro ,

Please click "Start Tracing Now" and begin your tracing, and then check it again.

BR,
Mia

If the answer is helpful, please click "Accept Answer" and upvote it.

Derek Lauro 1 Reputation point

2020-11-13T14:07:13.85+00:00

I'm not looking to log trace. See my screenshot - the tmp files that I highlighted in yellow are what I would like to have generate in a new directory.

Answer 3

m 4,276

Hi @Derek Lauro ,

I'm not looking to log trace. See my screenshot - the tmp files that I highlighted in yellow are what I would like to have generate in a new directory.

Yes. I know.
I mean do you click "Start Tracing Now" lab when you begin collect the log?

BR,
Mia

If the answer is helpful, please click "Accept Answer" and upvote it.

Share via

Microsoft ODBC Driver 17 for SQL Server - Query Result TEMP Storage

3 answers

Your answer