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.
Asked
Active
Viewed 725 times
1
-
1What have you researched, tried, and found to not provide an answer? – dfundako Dec 27 '16 at 20:17
-
See the script on this page. https://gallery.technet.microsoft.com/scriptcenter/Temporary-Tables-and-Their-aca7312a – tlemaster Dec 27 '16 at 20:47
1 Answers
0
@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.
plditallo
- 701
- 2
- 13
- 31
-
it was a year end one time query. We want to find the query so we can tune it, is there a way to find the query which took all the temp spaces from history stats? – user1595858 Dec 28 '16 at 03:04
-
Yes. Do you think it is still running? If it is, start with @sp_who2. Look at the processes in-flight. They'll all have an assigned identifier called a spid. The status will help point you to what is active, as will the command (e.g. Select, Insert, Update, etc.). Other hints will be the host name (server, desktop or laptop) and the user (login). When you find a spid of interest, type in dbcc inputbuffer(spid) -- spid being the actual spid #. There are several other diagnostic views available in SQL server to further analyze performance, latency, memory/io consumption. – plditallo Dec 28 '16 at 15:03
-
To help you get started on DMVs: https://msdn.microsoft.com/en-us/library/ms188754.aspx?f=255&MSPPError=-2147217396 – plditallo Dec 28 '16 at 15:04