How to identify tempdb space usage

Sometimes our tempdb gets full and fills the disk. Hence it cant expand and due to its a lots of our operation either stucks or starting failing.
At this time we immediately need to find which process is taking so much space in tempdb and which type of  operation by that process is taking space to troubleshoot the issue ASAP



Lets see how we can get this details

sys.dm_db_task_space_usage

SELECT TOP 5 *

FROM   sys.dm_db_task_space_usage

ORDER  BY ( user_objects_alloc_page_count + internal_objects_alloc_page_count ) DESC

Returns information for the pages allocated and deallocated by the running task 

Here we can see that session 164 is taking highest space and its taking most of space due to internal objects.So either worktables or sorting operation are reason for it. Immediately we can stop this process and optimize it to reduce load on tempdb

From BOL

user_object_reserved_page_count

User-defined tables and indexes
System tables and indexes
Global temporary tables and indexes
Local temporary tables and indexes
Table variables
Tables returned in the table-valued functions


Internal Objects

Work tables for cursor or spool operations and temporary large object (LOB) storage
Work files for operations such as a hash join
Sort runs

Version_store_reserved_page_count
It shows  pages used for version store in database.

There are some other system tables also which can give us additional information 

sys.dm_db_file_space_usage

 

USE tempdb

 SELECT *

 FROM   sys.dm_db_file_space_usage(nolock)

  
Here it gives space used by different objects in database file
 We have to look at user_object_reserved_page_count ,version_store_reserved_page_count,internal_object_reserved_page_count
 By looking at this value we can get idea what is taking space in tempdb

Here for above results the space is taken mostly by internal objectsSo its done by either work tables for the query or sort operations.