sys.dm_db_file_space_usage

  • 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).

  • 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!

  • 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?

  • 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 3 years, 10 months ago by  DBAless.
    • This reply was modified 3 years, 10 months ago by  DBAless.
  • 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!

  • 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)

  • 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.

     

     

     

     

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

    Cheers!

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

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