data file size difference

  • why is that when I run the sp_help (database name) or any query which gives datafile/log file name and size, it mentioned one value

    but when I go to the path where the data file is and check on property it gives another value

    I tried to restore the db but it still no change

    any views on how to troubleshoot this

  • Can you explain more what you are doing and seeing? What differences do you see? When you look in the folder, it's unclear what you are checking here. Is it the file size in bytes? Depending on what you are looking at, things might be different.

    for example, sp_helpdb gives me a size of 80.00MB for my Sandbox db. In the file system, I see

    • sandbox.mdf - 8,192kb
    • sandbox_log.ldf - 73,728kb

    That's 8192, and if I divide by 1024, I get the 80MB. You should be able to do something similar, but there might be some rounding difference, so this is why I'm curious for more details.

  • when I use sp_helpdb I get file size of 170GB. but when I go to the location where the mdf is placed it shows 160 GB

  • You are adding the log and data files together?

    There might be some rounding here, though that seems high. Can you provide exact values you see and how you are getting them?

  • no.. the data file and log files are  stored on separate drives. 170 GB from sp_helpdb (db name) query and 160 approx from the mdf location right click properties)

  • atulyan.aries wrote:

    when I use sp_helpdb I get file size of 170GB. but when I go to the location where the mdf is placed it shows 160 GB

    This might be a problem that has existed forever... some things use 1000^2 and 1000^3 for MB and GB totals and other things use 1024^2 and 1024^3 for MB and GB totals.

    I gave up trying to reconcile things like this about the 2nd time I saw them, which was back in the '70s some time. 😀

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • What size is your log file ?   xyz.LDF

    I just added up a database's MDF, NDF and LDF and everything matched up. when comparing sp_helpdb and the windows file info.

    • This reply was modified 1 month ago by  homebrew01.
    • This reply was modified 1 month ago by  homebrew01.

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

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