i dont understand the numbers - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

i dont understand the numbers

Author

Topic

inbs
Aged Yak Warrior

860 Posts

Posted - 2009-06-25 : 04:29:38

i have DATABASE :
recovery model simle
1.in properties of the DB :
size:45123.54 MB Space Svailable:1959.31 MB
2.in the folder(HD)
Data size:44583040 (KB) , the log size:1623488(KB)
3.in select * from sysfiles
Data size:5572880(??) , the log size:202963(??)

what is connection between the numbers ?
how can i know that 5572880 is 45123.54 MB
what is measure of 5572880??

thanks

ahmad.osama
Posting Yak Master

183 Posts

Posted - 2009-06-25 : 09:35:26

quote:
Originally posted by inbs

i have DATABASE :
recovery model simle
1.in properties of the DB :
size:45123.54 MB Space Svailable:1959.31 MB
2.in the folder(HD)
Data size:44583040 (KB) , the log size:1623488(KB)
3.in select * from sysfiles
Data size:5572880(??) , the log size:202963(??)

what is connection between the numbers ?
how can i know that 5572880 is 45123.54 MB
what is measure of 5572880??

thanks

can u give the complete result set of sysfiles...

inbs
Aged Yak Warrior

860 Posts

Posted - 2009-06-25 : 14:01:12

DATA:
fileid 1
groupid 1
size 5572880
maxsize -1
growth 32640
status 32770
perf 0
name FACT_Data
filename d:\....

LOG
fileid 2
groupid 0
size 256
maxsize -1
growth 10
status 1081410
perf 0
name FACT_Log
filename d:\....

i asked about the numbers ,why do you need result set of sysfiles?

mcrowley
Aged Yak Warrior

771 Posts

Posted - 2009-06-25 : 14:24:47

The SIZE column is measured in terms of 8KB pages. The filesystem gives you the size in terms of MB. You are looking at a Unit conversion problem.

inbs
Aged Yak Warrior

860 Posts

Posted - 2009-06-25 : 15:33:44

i see 44583040 * 8(KB) = 44583040 (KB)

so why in properties of the DB i see
45123.54 MB ; Space Svailable:1959.31 MB

tripodal
Constraint Violating Yak Guru

259 Posts

Posted - 2009-06-26 : 11:11:14

The file size (on disk) of your database is 45,123.54mb. The allocated space inside of this file has 1959.31mb left over for database growth. When the actual data consumes all 45,123mb of space in the file, Sql will need to grow the physical file. (you can configure this in enterprise manager or with a sql command i dont know). I believe the default for most versions is 10%, so as your database uses 45000mb of data. SQL will gro your file to 49500mb and have approximately 4500 space available..

inbs
Aged Yak Warrior

860 Posts

Posted - 2009-06-27 : 14:41:14

tripodal,

No,
The file size (on disk) of my database is:
44583040 (KB) , the log size:1623488(KB)

(IN SQL-DataBase i see 45,123.54mb)

why i have diffrent numbers 44583040(KB) against 45123.54 MB

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-27 : 17:57:40

The first step should be not to compare apples and oranges.


On disc in bytes data+log: 45,653,032,960 + 1,662,451,712 = 47,315,484,672
Sql says in bytes        :                                  47,315,461,079

So you can see it is nearly equal.

Can you be comfortable with these figures?

Fred

No, you're never too old to Yak'n'Roll if you're too young to die.

inbs
Aged Yak Warrior

860 Posts

Posted - 2009-06-28 : 02:29:26

sorry, but i still ddont understatnd,

in this link ,they say that unit of size is (KB of Page)
[url]http://doc.ddart.net/mssql/sql70/sys-f.htm[/url]

1.so i have 5572880, in what unit?
2.why i need to duplicat with 8 (5572880*8=44583040 (KB)) to get in KB?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-28 : 14:44:06

quote:
Originally posted by inbs

sorry, but i still ddont understatnd,

in this link ,they say that unit of size is (KB of Page)
[url]http://doc.ddart.net/mssql/sql70/sys-f.htm[/url]

1.so i have 5572880, in what unit?
2.why i need to duplicat with 8 (5572880*8=44583040 (KB)) to get in KB?

OK I will try again


Database properties (posted by inbs)
45,123.54 MB   =   45,123.54 * 1024 * 1024
==> 47,315,461,079 Byte
    ===================
------------------------------------------------------------

Sysfiles information (posted by inbs)
Data: 5,572,880 Pages = 5,572,880 * 8192 (1 Page=8192 Byte)
==> 45,653,032,960 Byte

Log:  202,963 Pages = 202,963 * 8192
==> 1,662,672,896 Byte

 45,653,032,960
+ 1,662,672,896
---------------
47,315,705,856 Byte
===================
------------------------------------------------------------

Folder (HD) (posted by inbs)
Data: 44,583,040 KB = 44,583,040 * 1024
==> 45,653,032,960 Byte

Log:  1,623,488 KB = 1,623,488 * 1024
==> 1,662,451,712 Byte

 45,653,032,960
+ 1,662,451,712
---------------
47,315,484,672 Byte
===================
------------------------------------------------------------

So you can see:

DB-Properties:  47,315,461,079 Byte
Sysfiles     :  47,315,705,856 Byte
Folder (HD)  :  47,315,484,672 Byte

I hope that now it is more clear to you.

Fred