How to find the sql that caused the temp db to fill the complete drive from history?

Question

We have noticed temp had filled up the drive on which it was placed. It had happened two days ago, is there a way to find the SQL that cause the temp do to grow. The SQL was failed as it couldn't get more space as it already filled up 250GB drive.

Answer 1

@user1595858, from a practical perspective, if your processes have normally been staying within the 250 GB range on the same server, it sounds like either a new deployment or something like a case for year-end processing in an existing deployment might be in play. If you've deployed the database to a new server, you will need to consider the temp space required for all the databases on that server. If you have deployed a new process or new database to the existing server now throwing the error, again you will need to re-evaluate the temp space required for all the databases on that server. Generally speaking, temp space that isn't explicitly invoked with writes to #myTempTable or ##myGlobalTempTable within sprocs/t-sql scripts get implicitly consumed when your processes require memory that isn't available--like scanning through heaps with large data sets on inefficient joins--or negating the benefits of indices by using functions in WHERE clauses.

From an analytical perspective, look at the execution plan of the query(ies) involved to give you clues as to where the improvements are needed. Start with the surrounding text of the error which will help you isolate where to begin.