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?

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