SqlServer : All Files In Instance ( Total, Used, Free )

by bitznbitez

Here is a query to find all of the files in all databases on a given sqlserver instance.   It will report file size, used and free.

Now we all know you are not supposed to shrink database files as a general rule.   If you do you will definitely need the query in my last post that identifies fragmentation so you can fix the problems shrinking creates.    Still there are times where you might want to identify what files are potential canidates for resizing etc.

create table #tempFreeSpace
  (
     [databasename] varchar(128)
    ,[fileid] integer
    ,[file_size_mb] decimal(10,2)
    ,[space_used_mb] decimal(10,2) 
    ,[free_space_mb] decimal(10,2)
    ,[name] varchar(128)
    ,[filename] varchar(512)
  );

Exec sp_msforeachdb '
  use [?]

  insert into #tempFreeSpace (
     [databasename] 
    ,[fileid] 
    ,[file_size_mb] 
    ,[space_used_mb] 
    ,[free_space_mb] 
    ,[name] 
    ,[filename] 
  )

  select
    db_name() as DatabaseName
    ,a.FILEID
   ,[FILE_SIZE_MB] = 
      convert(decimal(12,2),round(a.size/128.000,2))
   ,[SPACE_USED_MB] = 
      convert(decimal(12,2),round(fileproperty(a.name,''SpaceUsed'')/128.000,2))
   ,[FREE_SPACE_MB] = 
      convert(decimal(12,2),round((a.size-fileproperty(a.name,''SpaceUsed''))
      /128.000,2)) 
   ,NAME = left(a.NAME,15)
   ,FILENAME = left(a.FILENAME,256)

  from dbo.sysfiles a

'

select * from #tempFreeSpace order by free_space_mb desc;

drop table #tempFreeSpace;

You can always comment out the drop table so you can perform different filters and sorts against this temp table first.

Advertisements