SQL Server Tempdb Log File Growth – Causes and Solutions

sql servertransaction-log

I just started at my new job as a DBA. All of a sudden, we started getting alerts about disk space. When looked into it, I found that tempdb log file was growing continuously. There was an active transaction running against it. I checked the query and it was using temp tables. From 45 minutes, it hadn't progressed at all. I tried many things including these as a last resort. 

use tempdb
GO
DBCC FREEPROCCACHE -- clean cache
DBCC DROPCLEANBUFFERS -- clean buffers
DBCC FREESYSTEMCACHE ('ALL') -- clean system cache
DBCC FREESESSIONCACHE -- clean session cache

But it didn't help at all. I also killed the insert which was stuck. Seems like it was not out of the norm process. Finally I had to take an emergency outage of few minutes and restart SQL Service. Any tips about what I may have missed?

Best Answer

on this question:

How to identify which query is filling up the tempdb transaction log?

you will be able to see a lot more, in different ways and with more technical information as why this is happen and what to do regarding it.

below is a script that I have to check what is filling up the tempDB. it may be a good starting point for your quest.

use tempdb

SELECT top 100
tsu.internal_objects_alloc_page_count,
tsu.user_objects_alloc_page_count,
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
Related Question