TEMPDB ran out of space during spilling out in Azure Synapse SQL pool

Question

Hi All,

we are facing the Issue with TEMPDB full in Synapse SQL Pool. Due to this, our Daily jobs getting failed.#

Does anyone come across this issue? Kindly help to clear off TEMPDB space in synapse SQL pool.

Thanks in Advance

Answer 1

@Kalidas Yerravarapu - Thanks for the question and using MS Q&A platform.

To clear off TEMPDB space, you can try the following steps:

1. Monitor TEMPDB space usage using the Azure Synapse Toolkit to identify the queries that are consuming the most space.
2. If you have a query that is consuming a large amount of memory or have received an error message related to the allocation of tempdb, it could be due to a very large CREATE TABLE AS SELECT (CTAS) or INSERT SELECT statement running that is failing in the final data movement operation. This can usually be identified as a ShuffleMove operation in the distributed query plan right before the final INSERT SELECT. Use sys.dm_pdw_request_steps to monitor ShuffleMove operations.
3. The most common mitigation is to break your CTAS or INSERT SELECT statement into multiple load statements so that the data volume will not exceed the 399 GB per 100DWUc tempdb limit. You can also scale your cluster to a larger size to increase how much tempdb space you have.

For more details, refer to MS Q&A threads addressing similar issue:

Azure Synapse Error : Could not allocate a new page for database 'tempdb' because of insufficient disk space

Delete data from tempdb for Synapse dedicated SQL Pool

Troubleshoot tempdb errors on a dedicated SQL pool

Hope this helps. Do let us know if you any further queries.

---

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.