Give space usage of objects in tempdb (most likely this will be extended to more than just tempdb in a future edition of SQL Server.) Can be used to see how and why space is being used in Tempdb, on a file by file basis.

Type: View

Data: temporal, reflects the current state of the file usage

Columns:

• database_id – identifies the database (relates to sys.databases) (NOTE: only includes tempdb in 2005)
• file_id – the file identifier (relates to sys.database_files)
• unallocated_extent_page_count – Total number of pages that are located on unallocated extents (8 contiguous 8K pages) that are reserved in the file but not currently allocated to objects. Note that unused pages on extents that have any active data on them will not be reflected in the total.
• version_store_reserved_page_count – Number of pages that are reserved to support snapshot isolation transactions.
• user_object_reserved_page_count – Number of pages reserved to user tables
• internal_object_reserved_page_count – Number of pages reserved to internal objects, such as work tables that SQL Server creates to hold intermediate results
• mixed_extent_page_count – Number of extents that have pages of multiple types (user objects, version store, or internal objects, Index Allocation Map (IAM) pages, etc.)

Example:

The following query will show the number of pages allocated to each file in your tempdb, and how much space is allocated to the various purposes, or unallocated.

select mf.physical_name, mf.size as entire_file_page_count,
          dfsu.unallocated_extent_page_count,
          dfsu.user_object_reserved_page_count,
          dfsu.internal_object_reserved_page_count,
          dfsu.mixed_extent_page_count
from sys.dm_db_file_space_usage dfsu
          join sys.master_files as mf
                   on mf.database_id = dfsu.database_id
                         and mf.file_id = dfsu.file_id