Stored procedure => Tempbd has reached the has reached its size quota.

Question

Greeting all ,

i will try to simplify my probleme as much as i can .

im running multiple stored procedure in 1 SP :

ALTER PROCEDURE [dbo].[Load_DimFact_Tables]
EXEC [dbo].[Load_DIM1]
EXEC [dbo].[Load_DIM2]
EXEC [dbo].[Load_DIM3]
EXEC [dbo].[Load_FACT]
END

when i run the SP "Load_DimFact_tables" it returns the tempbd erreur . so i decied the run the SP's 1 by 1 .
The first 3 ones works fine , but when i run [dbo].[Load_FACT] the erreur appear .

im using Azure sql v12 Database . what i dont undrestand how can 1 SP consume all the tempbd in the database .

My question here can 1 SP use all the space in the tempdb ? my data source have arround 800.000 rows same as my FACT . im using a temp table and i make sure i drop it after the merge function . also i have few join in my SP (20 left join ) .

if yes , what is the best practice so i can optomize my SP so it doesnt take all my tempbd size .

Thanks in advance all

Answer 1

It appears that this is the same question as https://learn.microsoft.com/en-us/answers/questions/249109/error-the-tempdb-has-reached-its-size-db-in-azure.html?childToView=251609#comment-251609. No need to start a new thread.