Failed to allocate a new page for the database "TEMPDB due to insufficient disk space in the filegroup" DEFAULT

Question

Reports from ETL developers who tried to consistently execute our weekly and daily processes in ADW. Although in most cases they are executed without exception, I get this error:

"Failed to allocate a new page for the TEMPDB database due to insufficient disk space in the file group 'DEFAULT. Create the necessary space by dropping objects in the file group, adding additional files to the file group or setting automatic extension for existing files to the file group."

Is there a TEMPDB space limit associated with configuring DWU?

The database is limited to 100TB (for the portal) and is not full.

Answer 1

Azure SQL Data Warehouse allocates space for tempdb at a speed of about 399 GB per 100 DWU. Link here .

What DWU are you using at the moment? Consider temporarily raising your DWU goal or goal or refactoring your work to be less dependent on tempdb. Drop it when the batch process ends.

It is also possible to check your workload for any products, such as Cartesian products, over-sorting, over-reliance on temporary tables, etc., to see if work can be optimized.

Answer 2

Take a look at Explain Plans for your code and see if you have much more data traffic than you expect. If you find that one query has moved much more to Q-tables, you can configure it to avoid data movement (which may mean redesigning the tables for distribution in a different way).