TempDB running out of space is a common problem in SQL Server. The cause of this is varied but can usually be traced to a small number of executing commands which may be:
- Adding a large amount of data into TempDB directly
- Manipulating a large amount of data in a single transaction, where TempDB is being used as a sorting area
- Rebuilding indexes where TempDB is specified as a sorting area
For the period of time when TempDB is low in space other transactions using low amounts of TempDB storage may also fail due to the lack of available capacity. This makes identifying the root cause sometimes tricky as multiple transactions may fail.
To resolve the issue you need to understand the cause so you can take action to prevent in the future. However with TempDB when it runs out of space, the in progress transaction is typically rolled back (freeing space). Therefore by the time the DBA responds the in progress transactions have already cleared. Fortunately RockSolid monitors queries incurring TempDB usage and retains this data for retrospective analysis.
Firstly as with all RockSolid analysis, if TempDB runs out of space an alert will be raised in the RockSolid console. You do not need to monitor RockSolid reports to identify TempDB space issues. Therefore this investigative process assumes you have received an alert from RockSolid indicating a TempDB space issue has occurred.
- Go to the affected instance and review the TempDB space consumption history via the Analysis -> Ad-hoc tab. This will give you clues as to if this is a one off issue or space low space in TempDB is a reoccurring issue.
- Make note of the periods when space usage is high.
- Next go to the Analysis Tables tab
- Choose the TempDB queries from table the drop down and specify a date range which encompasses the time periods you identified above. If the time periods are not continuous you may do separately.
- Click show to list queries using TempDB then click on the TempDB Space(MB) column to sort in order of space consumption descending.
This table shows you all the queries that were executing during the specified period that were consuming TempDB space, and the amount of space being consumed. To resolve the TempDB space issues focus on improving the queries using the highest amounts of TempDB space first.