Convert sys.master_files size column to GB?

  • How can I Convert sys.master_files size column to GB?

    Select db_name(mf.database_id) As DatabaseName

    , convert(int

    , Case When convert(bigint , sum(mf.size)) >= 268435456 Then Null

    Else sum(mf.size) * 8 / 1000 -- Convert from 8192 byte pages to Kb

    --ELSE CONVERT(DECIMAL(10,2),(SUM(size * 8.00) / 1024.00 / 1024.00))

    End) As DatabaseSize_KB

    , cast(getdate() As date) As SizeDate

    From sys.master_files mf

    Where mf.state = 0

    Group By

    mf.database_id;

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Hi

    Try this

    SELECT

    DB.name,

    SUM(CASE WHEN type = 0 THEN MF.size * 8 / 1024.0 /1024.0 ELSE 0 END) AS DataFileSizeGB,

    SUM(CASE WHEN type = 1 THEN MF.size * 8 / 1024.0 /1024.0 ELSE 0 END) AS LogFileSizeGB

    FROM

    sys.master_files MF

    JOIN sys.databases DB ON DB.database_id = MF.database_id

    WHERE DB.source_database_id is null -- exclude snapshots

    GROUP BY DB.name

    ORDER BY DataFileSizeGB DESC

    Igor Micev,My blog: www.igormicev.com

  • That is sweet. I have the sizes stored in KB's. What statement could I used to convert from KB to GB?

    Thanks a bunch!

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Welsh Corgi (6/7/2014)


    That is sweet. I have the sizes stored in KB's. What statement could I used to convert from KB to GB?

    Thanks a bunch!

    Divide twice by 1024.0.

  • Lynn Pettis (6/7/2014)


    Welsh Corgi (6/7/2014)


    That is sweet. I have the sizes stored in KB's. What statement could I used to convert from KB to GB?

    Thanks a bunch!

    Divide twice by 1024.0.

    Syntax please? :unsure:

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Welsh Corgi (6/8/2014)


    Lynn Pettis (6/7/2014)


    Welsh Corgi (6/7/2014)


    That is sweet. I have the sizes stored in KB's. What statement could I used to convert from KB to GB?

    Thanks a bunch!

    Divide twice by 1024.0.

    Syntax please? :unsure:

    select 123456789/1024

    select 123456789/1024/1024

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • For fun, a quick demonstration

    😎

    /* 250 Gb */

    DECLARE @MY_NumberOfBytes NUMERIC(12,0) = 268435456000;

    /* bytes */

    SELECT

    'Bytes' AS UNIT

    ,@MY_NumberOfBytes AS NUMBER

    UNION ALL

    /* Kilobytes, divide by 2^10 (1024) */

    SELECT

    'Kilobytes' AS UNIT

    ,@MY_NumberOfBytes / POWER(2.0,10.0) AS NUMBER

    UNION ALL

    /* Megabytes, divide by 2^20 (1048576) */

    SELECT

    'Megabytes' AS UNIT

    ,@MY_NumberOfBytes / POWER(2.0,20.0) AS NUMBER

    UNION ALL

    /* Gigabytes, divide by 2^30 (1073741824) */

    SELECT

    'Gigabytes' AS UNIT

    ,@MY_NumberOfBytes / POWER(2.0,30.0) AS NUMBER

    UNION ALL

    /* Terabytes, divide by 2^40 (1099511627776) */

    SELECT

    'Terabytes' AS UNIT

    ,@MY_NumberOfBytes / POWER(2.0,40.0) AS NUMBER;

    Results

    UNIT NUMBER

    --------- ----------------------

    Bytes 268435456000.000000000

    Kilobytes 262144000.000000000

    Megabytes 256000.000000000

    Gigabytes 250.000000000

    Terabytes 0.244140625

  • Thanks everyone.:-)

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Eirikur Eiriksson (6/8/2014)


    For fun, a quick demonstration

    😎

    /* 250 Gb */

    DECLARE @MY_NumberOfBytes NUMERIC(12,0) = 268435456000;

    /* bytes */

    SELECT

    'Bytes' AS UNIT

    ,@MY_NumberOfBytes AS NUMBER

    UNION ALL

    /* Kilobytes, divide by 2^10 (1024) */

    SELECT

    'Kilobytes' AS UNIT

    ,@MY_NumberOfBytes / POWER(2.0,10.0) AS NUMBER

    UNION ALL

    /* Megabytes, divide by 2^20 (1048576) */

    SELECT

    'Megabytes' AS UNIT

    ,@MY_NumberOfBytes / POWER(2.0,20.0) AS NUMBER

    UNION ALL

    /* Gigabytes, divide by 2^30 (1073741824) */

    SELECT

    'Gigabytes' AS UNIT

    ,@MY_NumberOfBytes / POWER(2.0,30.0) AS NUMBER

    UNION ALL

    /* Terabytes, divide by 2^40 (1099511627776) */

    SELECT

    'Terabytes' AS UNIT

    ,@MY_NumberOfBytes / POWER(2.0,40.0) AS NUMBER;

    Results

    UNIT NUMBER

    --------- ----------------------

    Bytes 268435456000.000000000

    Kilobytes 262144000.000000000

    Megabytes 256000.000000000

    Gigabytes 250.000000000

    Terabytes 0.244140625

    Pretty slick/:cool:

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Igor Micev - Saturday, June 7, 2014 3:54 PM

    HiTry thisSELECT DB.name, SUM(CASE WHEN type = 0 THEN MF.size * 8 / 1024.0 /1024.0 ELSE 0 END) AS DataFileSizeGB, SUM(CASE WHEN type = 1 THEN MF.size * 8 / 1024.0 /1024.0 ELSE 0 END) AS LogFileSizeGBFROM sys.master_files MF JOIN sys.databases DB ON DB.database_id = MF.database_idWHERE DB.source_database_id is null -- exclude snapshotsGROUP BY DB.nameORDER BY DataFileSizeGB DESC

    Hats of to a job well done. I really appreciate T SQL code that works. It works perfectly since Microsoft two methods do not work.

Viewing 10 posts - 1 through 9 (of 9 total)

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