*.tmp file occupying space during select query

Question

question

ChiragSachdeva-6150 asked Mar 19, '21 ErlandSommarskog commented Mar 22, '21

*.tmp file occupying space during select query

Hi Folk,

On SQL Server 2012, I ran a below command. emp size is 9gb and there is no other table in testdb. Database/log is in C:\data folder. C drive had 14gb free space initially. emp has more than 15 million records. After letting select run for more than 1 minute, C: drive free space started to fall and after 3 minutes of select query Something occupied all free space of c: dirve and query failed with error message "An error occurred while executing batch. An error message is There is not enough space on the disk.". I found that .tmp file is consuming the space. Location of tmp file is C:\users\administrator\appdata\local\temp. As soon as i killed the session .tmp file got deleted and space got released back.

select * from testdb.dbo.emp

Can someone please share whats the use of tmp files? Why did they grow on running select? Are there any other scenarios which could make .tmp files grow? how do they get created?

Thanks in advance

reference screenshots.

Error message

tmp file space usage

sql-server-general

image.png (21.1 KiB)

image.png (218.8 KiB)

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 1 · 2021-03-21T10:40:13.24Z

ErlandSommarskog answered Mar 21, '21 JeffreyWilliams-3310 commented Mar 21, '21

I am not sure why you would need that link. I don't have any. I could google for one - but so could you.

But I will clarify one thing. We have a 32-bit application. A 32-bit application can at most address 4GB of memory without any extra tricks.

Way back in the old days, SQL Server itself did apply such tricks. Up to SQL Server 2008, SQL Server used something known as AWE, Address Window Extensions. This permitted 32-bit SQL Server to address a lot more than 4GB of memory for its buffer cache. The support for AWE was dropped when 64-bit server operating systems became the norm, and these days there is not even a 32-bit version of SQL Server.

SSMS does not use something like AWE, so to be able to cope with volumes beyond 4GB, it needs to do something else. I don't know for sure, but I assume that this temp file serves this purpose. That is, rather having all that 9GB of data in memory at once, it sends the data to a file, and as you scroll through the result set, it will read from that file.

Now you may ask: why is SSMS a 32-bit application? Shouldn't it be a 64-bit application? It absolutely should, but SSMS is based on the Visual Studio shell, and the Visual Studio team appears to have no plans to go 64-bit. Which probably makes sense for Visual Studio itself. But certainly not for SSMS.

You could try Azure Data Studio instead (which you get included when you install the most recent version of SSMS.) I am not particularly thrilled over ADS myself, but at least it is a 64-bit application.

· 1

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

JeffreyWilliams-3310 · Mar 21, 2021 at 04:33 PM

SSMS uses tmp files to hold the results that are returned from SQL Server - it does this for every query window. Azure Data Studio does the same thing - when you run a query the results will be held in a temp file. This is one thing to always be aware of when running queries - especially when running queries on the same system as the database engine.

1 ·

Answer 2 · 2021-03-19T23:30:30.33Z

ErlandSommarskog answered Mar 19, '21 ChiragSachdeva-6150 commented Mar 21, '21

That something that SSMS creates when receiving the result set.

Selecting all data of a 9GB table to SSMS is not wise thing to do. SSMS is a 32-bit application so it is likely to choke, unless the disk fills up quicker.

· 1

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ChiragSachdeva-6150 · Mar 21, 2021 at 04:04 AM

Hi @ErlandSommarskog thank you for your response. Is there any helpful link where I can read more about ssms creating tmp file. Thanks in advance.

0 ·

Answer 3 · 2021-03-22T07:05:26.997Z

CarrinWu-MSFT answered Mar 22, '21 ErlandSommarskog commented Mar 22, '21

Hi @techresearch7777777-7743,

what's the use of temp files?

Temporary files are created to hold data temporarily while a file is being created or processed or used. Please refer to Windows Temporary Files – Everything you want to know to get more information.

w
hy did they grow on running select?

There have some intermediate result keep growing. Such as the result of 'select' will temporary save in tempdb database, when you close SQL Server, the results will be deleted.

h
ow do they get created?

Windows Temporary files are created by the operating system during the normal course of its running when there may not be enough memory allocated for the task.

Best regards,
Carrin

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

· 1

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog · Mar 22, 2021 at 10:23 PM

<Hi @techresearch7777777-7743,

What has that user to do with this thread?

Such as the result of 'select' will temporary save in tempdb database, when you close SQL Server, the results will be deleted.

Results of SELECT queries are not stored in tempdb. And you don't "close" SQL Server.

0 ·

question