sys.dm_db_file_space_usage

Question

Post reply

sys.dm_db_file_space_usage

DBAless

Ten Centuries

Points: 1196
More actions
May 26, 2020 at 3:04 pm
Go to Answer
#3757453

I've been reading up on sys.dm_db_file_space_usage and see a lot of examples of how to convert the results to MB / GB. However, none these methods work for me... or if they do, I don't understand them.
When I run
use tempdb;
go
select * from sys.dm_db_file_space_usage;
For the total_page_count I get 85184.00.
That's converting to 332.75 GB. Which doesn't make sense to me. I believe I have a total of 40 GB of max space for all 8 tempdb files.
I can't find anything that would indicate the DMV would return a different value than the standard page count (8kb each).
Jacob Wilkins

One Orange Chip

Points: 27976
More actions
May 26, 2020 at 4:02 pm
Answer
#3757481

You don't believe your file is using 665.5 MB? Why not?
Why do you think you can't have 4.4 GB (4506 MB) free space?
To get GB, you would need to divide by (128*1024), not 256. There are 1024 MB in 1 GB, not 2.
Cheers!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 1

85184 pages is 665.5 MB

85184*8 KB= 681472 KB

681472 KB / 1024 (KB in a MB) = 665.5 MB

The process of *8/1024 simplifies to /128, so I usually just divide pages by 128 to convert to MB.

Cheers!

DBAless Ten Centuries Points: 1196 More actions · Answer 2

Yes, that's what I've seen on the internet. But I don't believe I have that much space in reality. To convert to GB you would divide by 256, right?

DBAless Ten Centuries Points: 1196 More actions · Answer 3

For example, the MS site says you can run this

SELECT SUM(unallocated_extent_page_count) AS [free pages],

(SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]

FROM tempdb.sys.dm_db_file_space_usage;

But I get these results which make even less sense

Free Pages 576832

Free Space in MB 4506.500000

This reply was modified 4 years, 6 months ago by DBAless.
This reply was modified 4 years, 6 months ago by DBAless.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 4

DBAless wrote:

Yes, that's what I've seen on the internet. But I don't believe I have that much space in reality. To convert to GB you would divide by 256, right?

No. Just to confirm what Jacob has stated, you would divide by (128*1024) or 131,072 to convert pages to GB.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

DBAless Ten Centuries Points: 1196 More actions · Answer 5

Thank you. I see how my math went wrong now on the conversion to GB.

Also, I've realized my assumptions were off. When I originally setup the tempdb into 8 files, I set each file at to it's max size (based on size of the drive set aside exclusively for the tempdb data files) and then didn't let it automatically grow. I did this when we had a dedicated physical server for this application.

It looks like IT has gone behind me, shrunk the files, and set them to auto-grow. They must have done this when the application was migrated to their virtual environment a few weeks ago. I'm guessing that's because the new storage solution can automatically allocate more drive space as needed. Instead of the 40GB of drive space being max value, it's now the virtual server's min value.

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 6

Jacob Wilkins

One Orange Chip

Points: 27976

May 26, 2020 at 4:54 pm

#3757493

No problem. I'm glad you got it sorted out.

Cheers!