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
tmp file space usage