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
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.
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:
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;
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
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:
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