Post reply

[?]..sysfiles.size Question

  • April 22, 2008 at 8:32 am

    #189309

    Could someone explain how to convert this size to gigabytes? I look at it, and I can't figure out what size it is in. Maybe bits?

    EXEC sp_MSForEachDB 'SELECT CONVERT(char(100), SERVERPROPERTY(''Servername'')) AS Server,

    ''?'' as DatabaseName,[?]..sysfiles.size, [?]..sysfiles.status, [?]..sysfiles.name, [?]..sysfiles.filename,convert(sysname,DatabasePropertyEx(''?'',''Status'')) as Status,

    convert(sysname,DatabasePropertyEx(''?'',''Updateability'')) as Updateability,

    convert(sysname,DatabasePropertyEx(''?'',''UserAccess'')) as User_Access,

    convert(sysname,DatabasePropertyEx(''?'',''Recovery'')) as Recovery From [?]..sysfiles '

    Any help is appreciated.

    -Kyle

  • April 22, 2008 at 1:13 pm

    #805713

    If you are trying to breakdown the size field of the SYSFILES system table to GB, here is a breakdown of how to do it.

    The size column is the number of 8KB extents that make of that file.

    So you first have to multiply the size by 8.0. I would definitely include the '.0' so that the results are a decimal.

    Divide this result by 1024.0 to go to MB and then divide these results by 1024.0 again to go to GB.

    Or just divide the size by 131072.0 to go straight to GB.

    ((size * 8.0) / 1024.0) / 1024.0

    or

    size / 131072.0

    Dave Novak

  • April 22, 2008 at 1:15 pm

    #805716

    Thank you so much.

    -Kyle

  • April 28, 2008 at 9:35 am

    #807894

    Dave, I am still getting the incorrect size when compared to the actual database properties. Is there a reason for this difference or is there a way to correct this? I thought initially that it was just the actual size vs the used space. However, this wasn't the case as some databases had 0 mb free space and the actual space was still incorrect.

    -Kyle

Viewing 4 posts - 1 through 3 (of 3 total)

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