4

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?

3

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
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.