Database growth Help

• sqlquest2575

  SSCommitted

  Points: 1802

  More actions

  August 12, 2011 at 3:41 pm

  #255659

  Hello Friends..Need you help in creating a script to capture a database growth and create a graph in excel...need to do this in a automated way..... I have multiple filegroups in the databse and and also a couple of table partitions...Could you guys please help.....

• Chuck Hottle

  SSCarpal Tunnel

  Points: 4777

  More actions

  August 12, 2011 at 4:12 pm

  #1368262

  This might give you a start.

  SELECT

  name = db_name(),

  ftype = case when fileproperty(f.name,'IsLogFile')=1 then 'LOG' else 'ROWS' end,

  fgroup = isnull(fg.groupname, 'Log'),

  sortid = isnull(nullif(f.groupid,0),9999),

  fname = f.name,

  fileid = f.fileid,

  MBgrowth= cast(case when f.status/0x100000&1=1 then size/128.0*(growth/100.0) else growth/128.0 end as decimal(19,2)),

  GrowBy = case when f.status/0x100000&1=1 then convert(varchar,growth)+'%' else 'MB' end,

  PCTused = convert(decimal(18,2), fileproperty(f.name, 'SpaceUsed')*100.0/f.size),

  BytesTotal= cast(size as bigint)*8192,

  BytesUsed = cast(fileproperty(f.name, 'Spaceused') as bigint) * 8192,

  BytesMax = case when maxsize>0 then cast(maxsize as bigint)*8192 else null end,

  FilePath = upper(left(f.filename,1))+RIGHT(RTRIM(f.filename),LEN(f.filename)-1)

  FROM sysfiles f

  LEFT JOIN sysfilegroups fg ON fg.groupid=f.groupid

• sqlquest2575

  SSCommitted

  Points: 1802

  More actions

  August 13, 2011 at 6:04 am

  #1368321

  Thank you so much Chuck...I have one question... Could you please explain below columns...

  Bytes Total -----Is this the total size of a table(I've multiple filegroup and it is showing size of each group)

  Bytes Used ----- is it the used space of a table ?

  Bytes Max ----Not sure about this one.....

  Please help