Skip to content

Query To Find SQL Server Database Properties

Below query will help you find details of database files (data and log) along with the file location, total space allocated to each database (to each database file), used space, database data and log files growth type (percentage or MB’s) as well as maximum database data and log file size.

This query will be helpful to find all the required details to host database on consolidated environment where multiple databases from different databases are hosted.

SELECT DatabaseName, FileName,
(case type_desc when 'ROWS' then 'DATA' else type_desc end) FileType,
physical_name FileLocation, size/128 CurrentSizeMB,
(case convert(varchar(20),max_size) when -1 then convert(varchar(20),'Unlimited') else convert(varchar(20),max_size/128) end) as MaxSizeMB ,
convert(varchar(20),growth) + (case convert(varchar(20),is_percent_growth) when 1 then convert(varchar(20),' Percent') else convert(varchar(20),' Pages of 8KB') end) as GrowthType
FROM master.sys.master_files MF, master.sys.databases D
where MF.database_id=D.Database_id
and not in ('Master','Model','Msdb','TempDB')