4

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?

  • Have you tried monitoring tempdb usage on the SSIS SQL Server during the load? – James Anderson Sep 2 '16 at 7:28
  • @JamesAnderson, this is an Azure database, the temdb is somehow hidden, how can I monitor its usage? – Jan Sep 5 '16 at 17:42
2

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:

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.

| improve this answer | |
  • 1
    The largest single TempDB file is 32MB (depending on service tier), but higher levels of service get more files. The OP should first try the insert with a smaller file to make sure and then change the insert process to work with smaller chunks of data at a time (assuming bulk insert or the like). – Jonathan Fite Jan 16 '18 at 12:46
0

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  
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.