SQL Server - What taks/queries should I need to execute when there is a lack of space in TempDB?

Question

I am having some troubles loading data to my fact table. Every time that I want to insert, I'm getting the following issue:

Could not allocate space for object 'dbo.SORT temporary run storage: 140739614015488' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

My question is: Which practices should I apply in order to understand why this error or to know if tempDB is close to full or not? There exists any queries that normally tell me some information about this?

Answer 1

Check your index rebuild plan, db.SORT usually indicates a SORT_IN_TEMPDB option being used and not enough space to accommodate it.

Answer 2

You can check current space used by sessions in tempdb using sys.dm_db_task_space_usage. For example, I use the following code to check out the current tempdb space usage:

CREATE view [sav].[vw_tempdb_usage] 
as     
select session_id, 
       cast(sum(internal_objects_alloc_page_count) * 8. /1024 /1024 as decimal(10,2))as internal_objects_alloc_Gb,
       cast(sum(internal_objects_dealloc_page_count) * 8. /1024 /1024 as decimal(10,2))as internal_objects_dealloc_Gb,
       cast(sum(user_objects_alloc_page_count) * 8. /1024 /1024 as decimal(10,2))as user_objects_alloc_Gb,
       cast(sum(user_objects_dealloc_page_count) * 8. /1024 /1024 as decimal(10,2))as user_objects_dealloc_Gb,
       cast(sum(internal_objects_alloc_page_count - 
                internal_objects_dealloc_page_count) * 8. /1024 /1024 as decimal(10,2))as internal_objects_diff_Gb,
       cast(sum(user_objects_alloc_page_count - 
                user_objects_dealloc_page_count)* 8. /1024 /1024 as decimal(10,2)) as user_objects_diff_Gb
from sys.dm_db_task_space_usage 
group by session_id
--having sum(internal_objects_alloc_page_count - internal_objects_dealloc_page_count + 
--      user_objects_alloc_page_count - user_objects_dealloc_page_count) /1024 > 0

select 
       p.spid,
       db_name(p.dbid) as db,
       p.loginame,
       p.hostname,
       p.program_name,
       p.login_time,
       v.internal_objects_diff_Gb,
       v.user_objects_diff_Gb,
       p.cmd,
       txt.text as txt,
       CASE WHEN encrypted = 1 THEN '-- ENCRYPTED'
               WHEN p.stmt_start >= 0
               THEN substring(txt.text, stmt_start/2 + 1,
                              CASE stmt_end
                                   WHEN -1 THEN datalength(txt.text)
                                   ELSE stmt_end/2 - stmt_start/2 + 1
                              END)
          END AS Statement
from s1057.sav.vw_tempdb_usage v join sys.dm_exec_sessions s 
        on v.session_id = s.session_id
     join sys.sysprocesses p
        on v.session_id = p.spid
     cross apply sys.dm_exec_sql_text(p.sql_handle)  txt 
where v.internal_objects_diff_Gb + v.user_objects_diff_Gb > 0

Here I created a view which counts the space used by user objects and system objects in Gb, then I run a code adainst this view that shows me all sessions with their executing statements that use more than 10Mb, expressed in Gb (2 digits after point); I put this code in a job that executes every 5 minutes and so I'm able to catch sessions that run out of space (we have 100Gb fixed tempdb space that is sufficient for our work, everything that runs out of this is anomal and must be investigated. To fill out 100Gb it takes more than 5 minutes so I'm always able to see who it was even if it happens off work hours)