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

 

 

 

Comments (0)

Skip to main content