While you can use sys.dm_db_file_space_usage to get the free space available in tempdb. you can use sys.dm_db_session_space_usage to get the used space by each session.

sys.dm_db_session_space_usage contains following columns:

1. session_id – session_id

2. database_id – will always be 2, id of tempdb

3. user_objects_alloc_page_count – number of pages allocated/reserved by the session

4. user_objects_dealloc_page_count – number of pages that are de-allocated by user objects for the session

for other columns returned by sys.dm_db_session_space_usage, refer BOL…

sys.dm_db_session_space_usage returns each row per session as below:

-- © 2011 – Vishal (http://SqlAndMe.com)
 
SELECT      session_id, user_objects_alloc_page_count,
            user_objects_dealloc_page_count
FROM        sys.dm_db_session_space_usage

Partial ResultSet:

session_id user_objects_alloc_page_count user_objects_dealloc_page_count
———-       —————————–                    ——————————-
1          0                             0
2          0                             0
3          0                             0
4          0                             0
5          0                             0
6          0                             0
7          0                             0
8          0                             0

Now, if I create a new object I should be able to see the same in the view:

USE AdventureWorks2008R2
 
SELECT      *
INTO        #TempTable
FROM        HumanResources.Employee
 
SELECT      @@spid

Result Set:

(290 row(s) affected)
 
——
52
 
(1 row(s) affected)

Now, if we check the DMV for session id = 52, we should be able to see the usage for that session:

SELECT      session_id, user_objects_alloc_page_count,
            user_objects_dealloc_page_count
FROM        sys.dm_db_session_space_usage
WHERE       session_id = 52

Result Set:

session_id user_objects_alloc_page_count user_objects_dealloc_page_count
———-       —————————–                    ——————————-
52         99                            90
 
(1 row(s) affected) 
 
(1 row(s) affected)

After dropping the #TempTable the counts will change as below:

session_id user_objects_alloc_page_count user_objects_dealloc_page_count
———-       —————————–                    ——————————-
52         99                            99
 
(1 row(s) affected)
  
(1 row(s) affected)

You can see here that de-allocated page count changed from 90 to 99.

Hope This Helps! Cheers!