Size of the database data file (not the log file) accurately

• MarvinTheAndriod

  SSCarpal Tunnel

  Points: 4972

  More actions

  July 14, 2009 at 9:04 am

  #136515

  Is there any T-sql that will return the size of the database data file (not the log file) accurately ?

• SQLRocker

  SSCrazy

  Points: 2217

  More actions

  July 14, 2009 at 9:12 am

  #1024480

  -- 2005 GUI,PDR etc give great info, 2008 is even cooler, gives info on SSMS object details only

  -- For 2000

  use databasename

  go

  select

  [FileSizeMB] =

  convert(numeric(10,2),round(a.size/128.,2)),

  [UsedSpaceMB] =

  convert(numeric(10,2),round(fileproperty( a.name,'SpaceUsed')/128.,2)) ,

  [UnusedSpaceMB] =

  convert(numeric(10,2),round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2)) ,

  [DBFileName] = a.name

  from

  sysfiles a