Post reply

Diskpace needed

  • July 7, 2008 at 7:12 am

    #123077

    hi guys, I am trying to figure out how much diskspace and memory i should need for the near future. Right now between all my databases i have around 150 GBs, what's the easiest way to find out how many filegroups and partitions i have in my server?

  • July 7, 2008 at 7:27 am

    #838866

    Use the sys.master_files view to get a list of all the files of all the databases known to SQL. It does contain the sizes of the files.

    Is that what you're after?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 7, 2008 at 7:31 am

    #838869

    Try this:

    sp_msforeachdb 'USE [?] select ''?'' as db_name, ''table_name''=object_name(i.id) ,i.indid

    ,''index_name''=i.name ,i.groupid

    ,''filegroup''=f.name ,''file_name''=d.physical_name

    ,''dataspace''=s.name from sys.sysindexes i

    ,sys.filegroups f ,sys.database_files d

    ,sys.data_spaces s

    where objectproperty(i.id,''IsUserTable'') = 1

    and f.data_space_id = s.data_space_id

    and f.data_space_id = d.data_space_id

    and f.data_space_id = i.groupid

    order by f.name,object_name(i.id),groupid '

    Maninder
    www.dbanation.com

  • July 7, 2008 at 7:32 am

    #838870

    there are several system tables that contain the required info.

    Example:

    select * from sys.database_files

    select * from sys.filegroups

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

  • July 7, 2008 at 7:32 am

    #838871

    I see several other posters allready beat me to it...

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

  • July 7, 2008 at 8:29 am

    #838935

    yes!!! , thank you all :), does partitioning come into play when deciding how much diskspace is needed?

  • July 7, 2008 at 8:33 am

    #838942

    Nothing comes into play except current size and growth. getting the sizes doesn't give you a clue. You would be better off tracking backup size growth for a few months to see how your dbs are changing.

    You should do this on a regular basis and then add/purchase space, yearly or every six months.

  • July 7, 2008 at 8:55 am

    #838964

    yes, i have a table which has the changes in sizes of all my databases, for the last 3 months, for the whole amount of diskspace i have in my server box, just go to the drives in the server right click and see the sizes? is there any hidden files?

  • July 7, 2008 at 9:11 am

    #838977

    No hidden files. MDF + NDF(s) + LDF is the database size. Don't forget backups.

  • July 7, 2008 at 9:19 am

    #838986

    thank you!!!

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

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