RMJCS Logo
 

sys.master_files

 
 

This view is handy because it is a single system-wide view - the sys.database_files view returns similar information for the current database. This view is the SQL Server 2005 replacement for the SQL Server 2000 system table sysaltfiles.

Note the BOL warning that file size figures may be inaccurate shortly after dropping or truncating large objects because of the deferred deallocation of pages.

There's some discussion in various forums on the Internet as to whether the file size figures returned by this view are accurate. In the checking I've done (with non-sparse files) I have yet to see any discrepancies between this view and file sizes reported by Windows Explorer but as a double-check the query below includes the file size info returned by sys.dm_io_virtual_file_stats as well.

 
     
 

Formatted query for sys.master_files

 
 
select
   db_name(mf.database_id) as database_name,
   --mf.file_id,
   --mf.file_guid,
   --mf.type,
   mf.type_desc,
   --mf.data_space_id,
   mf.name as logical_name,
   --mf.physical_name,
   --mf.state,
   mf.state_desc,
   (mf.size / 128) as mf_size_mb,
   (vfs.size_on_disk_bytes / 1024 / 1024) as vfs_size_mb,
   case
      when mf.max_size = -1 then 'unlimited'
      else cast((mf.max_size / 128) as varchar(32)) + 'mb'
   end as max_size,
   case
      when mf.is_percent_growth = 0 then cast((growth / 128) as varchar(32)) + 'mb'
      when mf.is_percent_growth = 1 then cast(growth as varchar(32)) + '%'
   end as growth,
   --mf.is_media_read_only,
   --mf.is_read_only,
   mf.is_sparse
   --mf.is_percent_growth,
   --mf.is_name_reserved,
   --mf.create_lsn,
   --mf.drop_lsn,
   --mf.read_only_lsn,
   --mf.read_write_lsn,
   --mf.differential_base_lsn,
   --mf.differential_base_guid,
   --mf.differential_base_time,
   --mf.redo_start_lsn,
   --mf.redo_start_fork_guid,
   --mf.redo_target_lsn,
   --mf.redo_target_fork_guid,
   --mf.backup_lsn
from
   sys.master_files mf
      left join sys.dm_io_virtual_file_stats(null,null) vfs on vfs.database_id = mf.database_id
                                                           and vfs.file_id = mf.file_id
order by
   mf.database_id,
   mf.file_id
 
     
Copyright 2007-2015 RMJCS Ltd