sys.dm_db_file_space_usage – Day 8 – One DMV a Day

Hello Geeks and welcome to the Day 8 of the long series of One DMV a day. In this series of blogs I will be exploring and explaining the most useful DMVs in SQL Server. As we go ahead in this series we will also talk about the usage, linking between DMVs and some scenarios where these DMVs will be helpful while you are using SQL Server. For the complete list in the series please click here.

With yesterday’s blog we have covered he IO related DMVs and today I am going to begin with the DMVs which will help you deal with the TempDB issues. There are three important DMVs which are specific to TempDB and are introduced only to help you troubleshoot and answer few questions when you need to restart as the final resort due to TempDB issues. 🙂 I will start with the most high level detail DMV, sys.dm_db_file_space_usage.

As the name suggests, sys.dm_db_file_space_usage will give you the information on how the space is being used inside each file in the TempDB. This is a point-in-time data which provides the current usage. From SQL Server 2012 Microsoft has introduced three new columns to give even more detailed information from the DMV.

The syntax again is straight forward and the most important columns to notice would be

filegroup_id (2012+) – Filegroup id to which the file belongs
total_page_count (2012+) – Total number of pages in the file (Allocated + Unallocated)
allocated_extent_page_count (2012+) – Allocated page count in allocated extents
unallocated_extent_page_count – count of unallocated pages only in unallocated extents
mixed_extent_page_count – Total number of pages in mixed extents
version_store_reserved_page_count – pages reserved (used an unused count in allocated extents) for version store
user_object_reserved_page_count – pages reserved for version store
internal_object_reserved_page_count – pages reserved for version store

Running sys.dm_db_file_space_usage would return the results like this. Use the first query if you are on SQL Server 2012 or above and the second query if you are on 2008 R2 and lower versions.

--SQL Server 2012 +
SELECT file_id,
FROM sys.dm_db_file_space_usage

--SQL Server 2008 R2 and lower versions till 2005
SELECT file_id,
FROM sys.dm_db_file_space_usage


From the above result you would know what kind of objects are occupying your tempdb space and act accordingly on the respective problem to fix any contention or space issues on your TempDB. You can say that internal objects are taking up more space and further troubleshoot. In combination with sys.dm_db_file_space_usage there are two other DMVs which will help you go into more detail about who is using up that space in TempDB. Tomorrow I will be covering one of those DMVs. So, stay tuned. Till then

Happy Learning,

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook |
Follow me on Twitter | Follow me on FaceBook

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter


About Manohar Punna

Manohar Punna is a Microsoft Data Platform Consultant and a Data Platform MVP. In his day to day job he works on building database and BI systems, automation, building monitoring systems, helping customers to make value from their data & explore and learn. By passion Manohar is a blogger, speaker and Vice President of DataPlatformGeeks. He is a community enthusiast and believes strongly in the concept of giving back to the community. Manohar is a speaker at various Data Platform events from SQL Server Day, various user groups, SQLSaturdays, SQLBits, MS Ignite, SSGAS & DPS. His One DMV a Day series is the longest one day series on any topic related to SQL Server available so far.

View all posts by Manohar Punna →

Leave a Reply

Your email address will not be published. Required fields are marked *