*.tmp file occupying space during select query

Chirag Sachdeva 281 Reputation points
2021-03-19T06:01:09.227+00:00

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
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,757 questions
0 comments
Accepted answer
  1. Erland Sommarskog 101.4K Reputation points MVP
    2021-03-21T10:40:13.24+00:00

    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 person found this answer helpful.

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 101.4K Reputation points MVP
    2021-03-19T23:30:30.33+00:00

    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 person found this answer helpful.
  2. CarrinWu-MSFT 6,856 Reputation points
    2021-03-22T07:05:26.997+00:00

    Hi @techresearch7777777 ,

    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.

    why 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.

    how 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.