THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Louis Davidson


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


  • 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.)


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,
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

Published Saturday, July 14, 2007 3:35 PM by drsql

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



James Luetkehoelter said:

Very nice Louis - I find that not many people know about this DM view. Especially given the potential load on TempDB under SQL 2005, this is one of the most common DMs I use.

July 16, 2007 11:34 AM

Aaron Bertrand said:

Hey Louis, I've found it more accurate to join against tempdb.sys.sysfiles than against sys.master_files.  At least in my testing so far, the former has running values, while the latter just has config values.

July 20, 2007 8:28 AM

drsql said:

Aaron.  Interesting.  I actually started using sys.master_files because sys.database_files may not be 100% accurate if you are working with read only databases.  I assume you are talking particularly about the size column, right?

July 27, 2007 9:36 PM

Pravin P said:

Thanks Louis. Is that unallocated extent is stay permenent in tempdb? when it will release to zero?

September 5, 2011 4:27 AM

Leave a Comment


This Blog


Links to my other sites


Privacy Statement