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.

Distribution Cleanup failed due to security reasons

Recently we got following error in Distribution Cleanup for Replication 


Replication-@rowcount_only parameter must be the value 0,1, or 2. 0=7.0 compatible checksum. 1=only check rowcou: agent distribution@rowcount_only parameter must be the value 0,1, or 2. 0=7.0 compatible checksum. 1=only scheduled for retry. Could not clean up the distribution transaction tables.

the job was failing and we are getting this error in the job history

Could not remove directory ‘E:\MSSQL\ReplData\unc\xx_xxx_xx\20121010114212\’. Check the security context of xp_cmdshell and close other processes that may be accessing the directory. [SQLSTATE 42000] (Error 20015) Replication-@rowcount_only parameter must be the value 0,1, or 2. 0=7.0 compatible checksum. 1=only check rowcou: agent distribution@rowcount_only parameter must be the value 0,1, or 2. 0=7.0 compatible checksum. 1=only scheduled for retry. Could not clean up the distribution transaction tables. [SQLSTATE 01000] (Message 14152).  

AS this message suggests its clearly related to security reson. 
The account which is used in SQL Server agent doesn’t have access to the folder for  E:\MSSQL\ReplData.

In such a case you can give full access on E:\MSSQL\ReplData  to the account which is used in SQL Server Agent  
Or
You can give access  to the users role on the folder, if you are not sure which account is used in SQL Server Agent account.