I have a BCP load process which failed last night saying short of space at tempdb.
When I had a look this morning I have more than 120 GB free on the volume where tempdb is located. The total size of volume is 300GB.
The files on tempdb device are still of size 10GB. I configured them with "unrestricted Growth" so they should take remaining 120GB free when needed.
My question is "Why do I have an error in the first place"?
Does tempdb files shrink automatically after the transaction using them has finished?
Can I use any DMV to locate space usage for last 24 hours? If not how do I know what went wrong with last night's process?