T-SQL Tuesday #78– sys.dm_db_file_space_usage

It’s T-SQL Tuesday,  the blog party that SQL Server expert Adam Machanic (blog|twitter) started. This month’s episode is hosted by Wendy Pastrick (blog| twitter). The topic: Learn something new and blog about it

SQL Server 2016 is coming out next month, and it’s chock full of new features that I can learn. Row-level security is interesting. Dynamic data masking sounds fun.

If you’re a regular reader of my blog, you probably know I try to approach questions from a unique angle. Instead of blogging about something cutting edge or sexy, I decided to scroll through the list of system views until I found one I didn’t recognize.

sys.dm_db_file_space_usage

The name is pretty self-explanatory, but I never noticed this existed until now. Seems like the type of DMV that I should have known about, but I didn’t. Quick look at BOL, and I got the verbose description from Microsoft:

Returns space usage information for each file in the database.

Thanks, Captain Obvious.

The column list gives pretty much what you’d expect given the name and BOL description:

  • database_id
  • file_id
  • filegroup_id
  • total_page_count
  • allocated_extent_page_count
  • unallocated_extent_page_count
  • etc…

What I usually do

For years, I’ve been doing something like this to get that type of info:

SELECT    
    db_id() AS database_id,
    file_id,
    data_space_id,
    CAST(size/128.0 AS decimal(20,2)) AS FileSizeMB, 
    CAST(FILEPROPERTY(name, 'SpaceUsed')/128.0 as decimal (20,2)) AS SpaceUsedMB, 
    CAST(size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed' ) AS int)/128.0 AS int) AS FreeSpaceMB
FROM sys.database_files;

 

The FILEPROPERTY stuff to get space used makes this a little clunky. The free space amount is actually a calculation, since there’s no explicit free space number from FILEPROPERTY or sys.database_files. It’s the type of voodoo that a less experienced person gets tripped up on. If I use sys.dm_db_file_space_usage, I can make that code just a little bit cleaner, with less voodoo and math:

SELECT
    database_id,
    file_id,
    filegroup_id,
    CAST(total_page_count/128.0 AS decimal(20,2)) AS FileSizeMB,
    CAST(allocated_extent_page_count/128.0 AS decimal(20,2)) AS SpaceUsedMB,
    CAST(unallocated_extent_page_count/128.0 AS decimal(20,2)) AS FreeSpaceMB
FROM sys.dm_db_file_space_usage;

 

There’s a catch

Were you following along? Did you realize why they aren’t the same?

sys.dm_db_file_space_usage

My “old school” query includes information about the transaction log, but sys.dm_db_file_space_usage only shows the data files. That’s not necessarily good or bad–it’s just different.

But wait, there’s more!

There are three more interesting columns in this DMV:

  • version_store_reserved_page_count
  • user_object_reserved_page_count
  • internal_object_reserved_page_count

You can use these columns to keep an eye on what is using space in TempDb. If you are troubleshooting a full TempDb, you can use this info to immediately know if you should be looking at user objects or the version store.

You turn — Go learn something new

Maybe you can learn ukulele

3 Comments

  1. Great post Andy!

    The BOL link mentions “IAM pages are not included, because they are always allocated from mixed extents. PFS pages are included if they are allocated from a uniform extent”.

    If you are using this to keep an eye on Tempdb, do you know of any differences outputted if you have TF 1118 on?

    • I really just stumbled on this DMV this week (perhaps, re-learning something I forgot long ago?)… So I can’t answer that..perhaps someone else will come along with some more info.

1 Trackback / Pingback

  1. DB File Space Usage – Curated SQL

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.