It feels really good when I stretch my knowledge, do some research, trust the tried and true, make a recommendation and …IT WORKS! It really is a beautiful thing.
Most recent case involved ETL jobs. All the ETL work was handled by stored procedures that used temp tables to manipulate and insert data into a staging database. Problem was the tempdb was growing until it consumed all the disk space and the job wouldn’t complete. Acquiring additional storage was going to take some time, so action needed to be taken else thousands of employees would be twiddling their thumbs, waiting on the disks to arrive.
Examining the stored procedures I noticed that there was an explicit drop of the temp tables if they existed, at the beginning, but no explicit drop of the temp tables at the end. I suggested we add an explicit drop after each step throughout all the stored procedures in all of the jobs.
This resulted in the tempdb going from 160GB growth to 600MB growth.