data file size difference

Question

data file size difference

atulyan.aries

SSCrazy

Points: 2020
More actions
May 6, 2021 at 12:37 pm

#3880080

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

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

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

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 1

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.

atulyan.aries SSCrazy Points: 2020 More actions · Answer 2

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

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 3

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?

atulyan.aries SSCrazy Points: 2020 More actions · Answer 4

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)

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 5

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.

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

homebrew01 SSC Guru Points: 55548 More actions · Answer 6

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 3 years, 6 months ago by homebrew01.
This reply was modified 3 years, 6 months ago by homebrew01.