question

ChiragSachdeva-6150 avatar image
0 Votes"
ChiragSachdeva-6150 asked ErlandSommarskog commented

*.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
79484-image.png


tmp file space usage
79532-image.png


sql-server-general
image.png (21.1 KiB)
image.png (218.8 KiB)
5 |1600 characters needed characters left characters exceeded

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

ErlandSommarskog avatar image
1 Vote"
ErlandSommarskog answered JeffreyWilliams-3310 commented

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

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

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 Vote 1 ·
ErlandSommarskog avatar image
1 Vote"
ErlandSommarskog answered ChiragSachdeva-6150 commented

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

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

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 Votes 0 ·
CarrinWu-MSFT avatar image
0 Votes"
CarrinWu-MSFT answered ErlandSommarskog commented

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

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

<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 Votes 0 ·