https://social.msdn.microsoft.com/Forums/sqlserver/en-US/0e710dcb-9046-41d0-a6af-be8e118ec7fc/tempdb-is-growing-abnormallyhow-to-check-and-find-out-what-makes-it-grow?forum=sqldatabaseengine One of the most common causes for this that I see is a complex query that makes heavy usage of worktables during processing or makes heavy use of Common Table Expressions (CTE's) that reference each other in a recursive fashion. Often times these can be broken down into separate logical operations that use an intermediate temp table to avoid the large worktables generated during execution, but you have to know what query caused tempdb to blow up to do this. Some other things not mentioned would be to look at the DMV's to see what's allocated the most space: ' select spu.session_id ,s.ansi_defaults ,s.ansi_null_dflt_on ,s.ansi_nulls ,s.ansi_padding ,s.ansi_warnings ,s.arithabort ,s.authenticating_database_id ,r.blocking_session_id ,s.client_interface_name ,s.client_version ,r.command ,s.concat_null_yields_null ,r.connection_id ,s.context_info ,s.cpu_time ,spu.database_id ,s.date_first ,s.date_format ,t.dbid ,s.deadlock_priority ,r.dop ,t.encrypted ,s.endpoint_id ,r.estimated_completion_time ,r.executing_managed_code ,r.external_script_request_id ,r.granted_query_memory ,s.group_id ,s.host_name ,s.host_process_id ,spu.internal_objects_alloc_page_count ,spu.internal_objects_dealloc_page_count ,s.is_user_process ,s.language ,s.last_request_end_time ,s.last_request_start_time ,s.last_successful_logon ,s.last_unsuccessful_logon ,r.last_wait_type ,s.lock_timeout ,s.logical_reads ,s.login_name ,s.memory_usage ,r.nest_level ,s.nt_domain ,s.nt_user_name ,t.number ,t.objectid ,r.open_resultset_count ,s.open_transaction_count ,s.original_login_name ,s.original_security_id ,r.parallel_worker_count ,r.percent_complete ,r.plan_handle ,s.prev_error ,s.program_name ,r.query_hash ,r.query_plan_hash ,s.quoted_identifier ,s.reads ,r.request_id ,s.row_count ,r.scheduler_id ,s.security_id ,r.sql_handle ,r.start_time ,r.statement_context_id ,r.statement_end_offset ,r.statement_sql_handle ,r.statement_start_offset ,s.status ,r.task_address ,t.text ,s.text_size ,s.total_elapsed_time ,s.total_scheduled_time ,r.transaction_id ,s.transaction_isolation_level ,s.unsuccessful_logons ,r.user_id ,spu.user_objects_alloc_page_count ,spu.user_objects_dealloc_page_count ,spu.user_objects_deferred_dealloc_page_count ,r.wait_resource ,r.wait_time ,r.wait_type ,s.writes from sys.dm_db_session_space_usage spu join sys.dm_exec_sessions s on s.session_id = spu.session_id join sys.dm_exec_requests r on s.session_id = r.session_id cross apply sys.dm_exec_sql_text(sql_handle) t order by spu.internal_objects_alloc_page_count desc
