Azuredb The database 'tempdb' has reached its size quota

Question

We are runnig a V12 Azure Database instance on the S3 tier. There is still about 100GB of a free space on the database. When loading 85MB XML file with SSIS running on a different non azure SQL server, and directly inserting it into the azure database, the insert crashes on the target database with the following error.

The database 'tempdb' has reached its size quota. Partition or delete data, drop indexes, or consult the documentation for possible resolutions.

Is there any limit on the tempdb or any idea why this might be crashing? There is no way how the 85MB file can fill the remaining space on the database. The tempdb seems somehow hidden, how can I monitor its usage?

Answer 1

Limits depend from your service trier, in this sitiation it is no less than 250 GB for database, but it is only 32 MB for tempdb attached to this database. You can check other available resources here Azure SQL Database resource limits and information about Azure tempdb here Tempdb database in SQL Database.

There are several options which allow you to investigate what is going on with your tempdb. It is hidden, but you can still query sys objects to retrive information about this kind of databases. Try to check following resources:

• Lesson Learned #10: Monitoring TempDB usage (useful answer in comment, one tempdb per database)
• How to identify which query is filling up the tempdb transaction log?
• How to Monitor tempdb use

When you find out what is consuming tempdb resources, you should be able to fix it or extend your question with additional information.

As you said, your file is 85 MB and we can see that it exceed max tempdb data size. Try to work with smaller chunks of data at a time. You should also consider BULK insert in this scenario.

Answer 2

You can run the below to retrieve information about which tasks (in Azure V12 database) are using Tempdb (run from your userDB):

SELECT es.host_name , es.login_name , es.program_name
     , st.dbid as QueryExecContextDBID, DB_NAME(st.dbid) as QueryExecContextDBNAME
     , st.objectid as ModuleObjectId
     , SUBSTRING(st.text, er.statement_start_offset/2 + 1
                        ,(CASE WHEN er.statement_end_offset = -1 
                               THEN LEN(CONVERT(nvarchar(max),st.text))*2 
                               ELSE er.statement_end_offset
                          END - er.statement_start_offset)/2
                ) as Query_Text
     , tsu.session_id ,tsu.request_id, tsu.exec_context_id
     , (tsu.user_objects_alloc_page_count - tsu.user_objects_dealloc_page_count) as OutStanding_user_objects_page_counts
     , (tsu.internal_objects_alloc_page_count - tsu.internal_objects_dealloc_page_count) as OutStanding_internal_objects_page_counts
     , er.start_time, er.command, er.open_transaction_count
     , er.percent_complete, er.estimated_completion_time
     , er.cpu_time, er.total_elapsed_time, er.reads,er.writes
     , er.logical_reads, er.granted_query_memory 
FROM tempdb.sys.dm_db_task_space_usage tsu  
JOIN sys.dm_exec_requests er 
    ON tsu.session_id = er.session_id 
   AND tsu.request_id = er.request_id  
JOIN sys.dm_exec_sessions es 
    ON tsu.session_id = es.session_id   
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) st 
WHERE (tsu.internal_objects_alloc_page_count 
      +tsu.user_objects_alloc_page_count) > 0 
ORDER BY (tsu.user_objects_alloc_page_count -
          tsu.user_objects_dealloc_page_count)
       + (tsu.internal_objects_alloc_page_count - 
          tsu.internal_objects_dealloc_page_count) DESC