Error : The tempdb has reached its size (DB in azure )

Question

Greeting Guys .

im running a few Stored procedure via Data factory tasks , my database is on azure V12. everything went fine until yesterday where i got this error :

the Stored procedure that have this error contain a list of Stored procedure , so i was wondering that could be the problem .

i wanted to know does Stored procedure run one by one or at the same time ? if is it at the same time is at possible to make it run 1 by 1 ?

Thanks in advance

Answer 1

There is not that much difference to tempdb in Azure that it is on-prem. In both cases tempdb can grow until it hits some limit. On-prem that would be the amount of free space on the disk. Or any max size the DBA has configured for tile files. I would guess that the latter applies in Azure, and the exact size depends on what service level you have.

Tempdb can grow because of many things:

• Temp tables and table variables.
• Sort and hash spills.
• Spool operators (i.e. worktables).
• Version store.

I may have forgotten some.

If one particular SP fills up tempdb, I would assume that there is a query in that procedure that produces any of the two in the middle.

Answer 2

Your question is exactly clear, but for stored procedures to be running in parallel, you need multiple connections. Thus, if you call a stored procedure that runs other procedures, these procedures will be executed one at a time.

Answer 3

Thanks for ur answer Erland but one of the PS is causing the tempdb Issue .

Actually i dont undrestand Exactly How tempbd works in azure ! i use temp table within my stored procedure ! but i drop it after i use it ! so actually i dont undrestand how can my Stored procedure reach the maximum size

Thanks again

Answer 4

It could be that as your data grow, you finally came over the brink.

But more likely, you got a new query plan that starts to use disk more extensively.

You should be able to tell from the error message which statement that is failing. Then you could look in Query Store for plans to the query and as a quick solution force the old plan while you work with making the query more robust.