SQL TEMP DB Growth

Query that identifies the currently active T-SQL query, it’s text and the Application that is consuming a lot of tempdb space

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 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

===============
Identify which type of tempdb objects are consuming  space
The following query helps you understand if user objects or version store or internal objects are the ones using the space in tempdb. According to this output, you can focus on the below sections.

SELECT

SUM (user_object_reserved_page_count)*8 as user_obj_kb,

SUM (internal_object_reserved_page_count)*8 as internal_obj_kb,

SUM (version_store_reserved_page_count)*8  as version_store_kb,

SUM (unallocated_extent_page_count)*8 as freespace_kb,

SUM (mixed_extent_page_count)*8 as mixedextent_kb

FROM sys.dm_db_file_space_usage

If user_obj_kb is the highest consumer, then you that objects are being created by user queries like local or global temp tables or table variables. Also don’t forget to check if there are any permanent 

tables created in TempDB. Very rare, but I’ve seen this happening.

If version_store_kb is the highest consumer, then it means that the version store is growing faster than the clean up. Most likely there are long running transactions or open transaction (Sleeping state), 

which are preventing the cleanup and hence not release tempdb space back.

===================

SQL SERVER – Who is Consuming my TempDB Now?



SELECT

st.dbid AS QueryExecutionContextDBID,

DB_NAME(st.dbid) AS QueryExecContextDBNAME,

st.objectid AS ModuleObjectId,

SUBSTRING(st.TEXT,

dmv_er.statement_start_offset/2 + 1,

(CASE WHEN dmv_er.statement_end_offset = -1

THEN LEN(CONVERT(NVARCHAR(MAX),st.TEXT)) * 2

ELSE dmv_er.statement_end_offset

END - dmv_er.statement_start_offset)/2) AS Query_Text,

dmv_tsu.session_id ,

dmv_tsu.request_id,

dmv_tsu.exec_context_id,

(dmv_tsu.user_objects_alloc_page_count
- dmv_tsu.user_objects_dealloc_page_count) AS OutStanding_user_objects_page_counts,

(dmv_tsu.internal_objects_alloc_page_count
- dmv_tsu.internal_objects_dealloc_page_count) AS OutStanding_internal_objects_page_counts,

dmv_er.start_time,

dmv_er.command,

dmv_er.open_transaction_count,

dmv_er.percent_complete,

dmv_er.estimated_completion_time,

dmv_er.cpu_time,

dmv_er.total_elapsed_time,

dmv_er.reads,dmv_er.writes,

dmv_er.logical_reads,

dmv_er.granted_query_memory,

dmv_es.HOST_NAME,

dmv_es.login_name,

dmv_es.program_name

FROM sys.dm_db_task_space_usage
dmv_tsu

INNER JOIN sys.dm_exec_requests dmv_er

ON (dmv_tsu.session_id = dmv_er.session_id AND dmv_tsu.request_id = dmv_er.request_id)

INNER JOIN sys.dm_exec_sessions dmv_es

ON (dmv_tsu.session_id = dmv_es.session_id)

CROSS
APPLY
sys.dm_exec_sql_text(dmv_er.sql_handle) st

WHERE (dmv_tsu.internal_objects_alloc_page_count
+ dmv_tsu.user_objects_alloc_page_count) > 0

ORDER BY (dmv_tsu.user_objects_alloc_page_count
- dmv_tsu.user_objects_dealloc_page_count) + (dmv_tsu.internal_objects_alloc_page_count
- dmv_tsu.internal_objects_dealloc_page_count) DESC
==============================

SELECT

percent_complete,

start_time,

status,

command,

estimated_completion_time,

cpu_time,

total_elapsed_time

FROM

sys.dm_exec_requests

WHERE

command
= 'DbccFilesCompact'



==================================