4

ETL developer reports they have been trying to run our weekly and daily processes on ADW consistently. While for the most part they are executing without exception, I am now getting this error:

“Could not allocate a new page for database ‘TEMPDB’ because of insufficient disk space in filegroup ‘DEFAULT’. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.”

Is there a limit on TEMPDB space associated with the DWU setting?

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

Steve
  • 548
  • 8
  • 24

2 Answers2

2

Azure SQL Data Warehouse does allocate space for a tempdb, at around 399 GB per 100 DWU. Reference here.

What DWU are you using at the moment? Consider temporarily raising your DWU aka service objective or refactoring your job to be less dependent on tempdb. Lower it when your batch process is finished.

It might also be worth checking your workload for anything like cartesian products, excessive sorting, over-dependency on temp tables etc to see if any optimisation can be done.

wBob
  • 13,710
  • 3
  • 20
  • 37
  • 1
    I had bumped the DWU up twice with no affect. Developer said query had been running for some time (but I can only trust that to be the case). The query was attempting a self join of a table with a inequality condition involved. I think the root of the problem is poor/wrong logic in the query as the grain of the table did not match what I was expecting for such a use case. – Steve Nov 29 '16 at 22:36
2

Have a look at the Explain Plans for your code, and see whether you have a lot more data movement going on than you expect. If you find that one query does moved a lot more into Q tables, you can probably tune it to avoid the data movement (which may mean redesigning tables to distribute in a different key).

Rob Farley
  • 15,625
  • 5
  • 44
  • 58