Lesson Learned #10: Monitoring TempDB usage

We are receiving several support cases when our customers are getting the following error message "The database 'TEMPDB' has reached its size quota. Partition or delete data, drop indexes, or consult the documentation for possible resolutions." and their query ends with an exception.

As any SQL Server instance every Azure SQL Database Tier has a limitation for the TEMPDB capacity, normaly, the best way to resolve the issue to increase a higher database tier, but, if you need to identify the query/queries and their TEMPDB consumption per each one, please, run the following TSQLs to obtain the details.

SELECT 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,es.host_name , es.login_name , es.program_name FROM sys.dm_db_task_space_usage tsu INNER JOIN sys.dm_exec_requests er ON ( tsu.session_id = er.session_id AND tsu.request_id = er.request_id) INNER 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

 

Other queries that you could use to obtain more information are:

SELECT * FROM sys.dm_db_session_space_usage
SELECT * FROM sys.dm_db_task_space_usage
SELECT * FROM sys.dm_db_file_space_usage