Wednesday, March 9, 2011

Get Any Database Physical File Name, File Type, File Size, Max File Size, Growth Etc

Here is the script which I was wrote for collecting bunch of information related to all the databases in the current server like DB Name, Physical File, File Type, File Size, Max File Size, Growth, Etc. Just copy the below script, paste it in your New Query window and execute...

Here is the script:

Name: Nareshkar Pakanati (
SELECT DB_NAME(database_id) AS DatabaseName,
CAST([Name] AS varchar(20)) AS NameofFile,
CAST(physical_name AS varchar(100)) AS PhysicalFile,
type_desc AS FileType,
((size * 8)/1024) AS FileSize,
MaxFileSize = CASE WHEN max_size = -1 OR max_size = 268435456 THEN 'UNLIMITED'
WHEN max_size = 0 THEN 'NO_GROWTH'
WHEN max_size <> -1 OR max_size <> 0 THEN CAST(((max_size * 8) / 1024) AS varchar(15))
ELSE 'Unknown'

SpaceRemainingMB = CASE WHEN max_size = -1 OR max_size = 268435456 THEN 'UNLIMITED'
WHEN max_size <> -1 OR max_size = 268435456 THEN CAST((((max_size - size) * 8) / 1024) AS varchar(10))
ELSE 'Unknown'
Growth = CASE WHEN growth = 0 THEN 'FIXED_SIZE'
WHEN growth > 0 THEN ((growth * 8)/1024)
ELSE 'Unknown'
GrowthType = CASE WHEN is_percent_growth = 1 THEN 'PERCENTAGE'
WHEN is_percent_growth = 0 THEN 'MBs'
ELSE 'Unknown'
FROM master.sys.master_files
WHERE state = 0
AND type_desc IN ('LOG', 'ROWS')
ORDER BY database_id, file_id 

Output will be looks like below screenshot: 

SQL Server Version. Microsoft SQL Server 2012. 2008 R2. 2008. 2005. 2000 and 7.0 Builds. How do I know which version of SQL Server I'm running. SQL Server Version History. Find your Installed SQL Server Versions and Instances. Files and Filegroups Architecture. Physical Database Files and Filegroups. Get Physical File Path of Any Database. Move Database Files in SQL Server. SQL Server 2012. Get SQL Server Database Physical file Location and file size using. Unable to open physical file. in sql server 2008 R2. in sql server 2005.  SQL Physical Database File Location. Finding Location of Log File. Finding Location of Data File.


Post a Comment