Post reply

Getting files size for each database file

  • October 28, 2014 at 9:07 am

    #297803

    Hi, I have the query below. I would like to add to it the actual file size in mb or gb of each file to the results. I am not very good at tsql so this is as far as I have been able to get on my own.

    select sd.name,mf.name as logical_name,mf.physical_name,

    case

    when dm.mirroring_state is null then 'No'

    else 'Yes'

    end as Mirrored

    from sys.sysdatabases sd JOIN

    sys.master_files mf on sd.dbid = mf.database_id

    join sys.database_mirroring dm on sd.dbid = dm.database_id

    The sp_spaceused procedure does a nice job on it's own giving me what I want (only one db though), plus a bonus allocated space column. How can I combine this sp with my other query, or is there a better way to ad this information? Thanks for any help.

  • October 28, 2014 at 9:30 am

    #1755263

    The column size describes the amount of pages that the file has. Page's size is 8K so if you multiply this column by 8, you get file's size by KB. Now all you have to do is divide this number by 1024.0 in order to get the size in MB.

    select size * 8 / 1024.0 as SizeInMB, * from sys.master_files

    Adi

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    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/

  • October 28, 2014 at 9:31 am

    #1755265

    Thanks 🙂

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

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