How full is my datafile?

• kevaburg

  SSCoach

  Points: 18131

  More actions

  October 12, 2012 at 6:47 am

  #265622

  Hi folks!

  When I create my database I define sizes for the datafiles that make it up, by default the MDF and LDF datafiles. I might specify 250MB for the MDF and 50MB for the LDF. These sizes are the sizes that are displayed when I look in Windows Explorer, run sp_helpdb and so on.

  The problem is this: How do I find out how much of that datafile is actually filled with data and how much is empty and reserved for use using SQL-based queries?

  Many thanks for your help!

  Regards,

  Kev

• durai nagarajan

  SSCoach

  Points: 15778

  More actions

  October 12, 2012 at 7:31 am

  #1548633

  This is the one i use.

  use master

  CREATE TABLE #TMPFIXEDDRIVES (

  DRIVE CHAR(1),

  MBFREE INT)

  INSERT INTO #TMPFIXEDDRIVES

  EXEC xp_FIXEDDRIVES

  CREATE TABLE #TMPSPACEUSED (

  DBNAME VARCHAR(50),

  FILENME VARCHAR(50),

  SPACEUSED FLOAT)

  INSERT INTO #TMPSPACEUSED

  EXEC( 'sp_msforeachdb''use ?; Select ''''?'''' DBName, Name FileNme, fileproperty(Name,''''SpaceUsed'''') SpaceUsed from sysfiles''')

  SELECT C.DRIVE,

  CASE

  WHEN (C.MBFREE) > 1000 THEN CAST(CAST(((C.MBFREE) / 1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' GB'

  ELSE CAST(CAST((C.MBFREE) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' MB'

  END AS DISKSPACEFREE,

  A.NAME AS DATABASENAME,

  B.NAME AS FILENAME,

  CASE B.TYPE

  WHEN 0 THEN 'DATA'

  ELSE TYPE_DESC

  END AS FILETYPE,

  CASE

  WHEN (B.SIZE * 8 / 1024.0) > 1000 THEN CAST(CAST(((B.SIZE * 8 / 1024) / 1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' GB'

  ELSE CAST(CAST((B.SIZE * 8 / 1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' MB'

  END AS FILESIZE,

  CAST((B.SIZE * 8 / 1024.0) - (D.SPACEUSED / 128.0) AS DECIMAL(15,2)) SPACEFREE,

  B.PHYSICAL_NAME

  FROM SYS.DATABASES A

  JOIN SYS.MASTER_FILES B

  ON A.DATABASE_ID = B.DATABASE_ID

  JOIN #TMPFIXEDDRIVES C

  ON LEFT(B.PHYSICAL_NAME,1) = C.DRIVE

  JOIN #TMPSPACEUSED D

  ON A.NAME = D.DBNAME

  AND B.NAME = D.FILENME

  ORDER BY DATABASENAME asc

  DROP TABLE #TMPFIXEDDRIVES

  DROP TABLE #TMPSPACEUSED

  Regards
  Durai Nagarajan

• Perry Whittle

  SSC Guru

  Points: 234040

  More actions

  October 12, 2012 at 8:05 am

  #1548657

  kevaburg (10/12/2012)

  ---

  How do I find out how much of that datafile is actually filled with data and how much is empty and reserved for use using SQL-based queries

  Run this per database

  selectdf.name AS LogicalFileName

  , isnull(fg.name, 'Log') AS FilegroupName

  , df.physical_name AS PhysicalOSName

  , (df.size * 8 / 1024) AS SizeMBs

  , (fileproperty(df.name, 'SpaceUsed') / 128) AS SpaceUsedMBs

  , (df.size * 8 / 1024) - (fileproperty(df.name, 'SpaceUsed') / 128) AS FreeMBs

  , case df.max_size

  when 0 then 'No Growth'

  when -1 then 'Unlimited'

  when 268435456 then '2TB'

  else cast(df.max_size / 128 AS VARCHAR(10)) + ' MBs'

  end AS MaxFileSize

  , case df.is_percent_growth

  when 0 then cast(df.growth / 128 AS VARCHAR(10)) + ' MBs'

  else CAST(df.growth AS VARCHAR(10)) + ' %'

  end AS Growth

  ,cast(cast((fileproperty(df.name, 'SpaceUsed') / 128) as numeric(20,2)) /

  cast(df.size / 128 as numeric(20,2))

  * 100 as numeric(20,2)) as PercentUsed

  from sys.database_files df left outer join sys.filegroups fg ON df.data_space_id = fg.data_space_id

  order by df.type

  -----------------------------------------------------------------------------------------------------------

  "Ya can't make an omelette without breaking just a few eggs"

• kevaburg

  SSCoach

  Points: 18131

  More actions

  October 15, 2012 at 6:47 am

  #1549104

  Hi there!

  Thank you very much! That was exactly what I was looking for!

  Kind regards,

  Kev:w00t: