Azure SQL Database Error 1104 - TEMPDB ran out of space during spilling in Azure Data Factory. Removed TempDB from the sink side, but fails during source.

Question

I am getting the following error when trying to write from one Azure SQL Database to Another using Azure Data Factory.

I researched this is a TempDB running out of space, so I first increased the size of the database as a quick fix which did not work.

I next tried to uncheck the "Use TempDB" box in Azure Data Factory on the sink and instead write to a staging schema. My query still is getting the 1104 error, and I am stuck on how to proceed. It seems like the failure is happening on the source side, but there is no option not to use the TempDB on the source.

Is there a way to clear the TempDB since it is running out of space? Is the only option to upgrade again? I wasn't having this problem for months in my pipelines, but for the last two weeks nothing is working with two of my main tables. These tables are processing ~15M rows.

Thanks for any advice or help

Edit:
The source service tier is "General Purpose - Serverless: Gen5, 10 vCores" - this is the one I increased from 8 vCore
The sink service tier is "General Purpose - Serverless: Gen5, 6 vCores" - I also increased this from 4 vCore

Answer 1

Hello service-pwrbiadmrpt,

Did you get a chance to look into the query for the optimization?

If possible, break the data processing into smaller batches. This can help reduce the amount of data being processed simultaneously and may alleviate the pressure on TempDB.

You can also use a dynamic management view ex: sys.dm_db_task_space_usage and sys.dm_db_session_space_usage to monitor TempDB usage.

Please see the below techlink article about how to resolve tempdb-related errors in Azure SQL Database

https://techcommunity.microsoft.com/t5/azure-database-support-blog/resolve-tempdb-related-errors-in-azure-sql-database/ba-p/3597944

I hope this helps. If you have any further questions, please let us know.