Info for Each Database File

Some of the sys tables are great at getting the minimum amount of data needed from them to get to the point. A good example is the sys.master_files, which contains a row per file of each databse on an instance.


SELECT name, physical_name from sys.master_files

However, there’s a lot more info available through that table, and it just requires a bit more effort to get the pertinent info out in a readable format. Below is a query that gives the file name, file path, the growth value of that file, how much it grows by and what it is its maximum size. I’ve added file type and status as they’re good to know plus added a join to the database table to get the database name.


;with  MasterFiles( [dbId], [File_Name],[File_Path], Size, MaxSize, Growth, percentageGrowth, [state], [type])
as
(
SELECT database_id, name, physical_name, size, max_size, growth, is_percent_growth, [state], [type]
FROM sys.master_files
)
select
db.[name]
,[File_Name]
,[File_Path]
, 'FileGrowth' =
CASE
WHEN Growth <= 128 AND percentageGrowth = 0 THEN CAST (convert(int,round(Growth*8.000,0)) AS NVARCHAR (256)) + ' KB' WHEN Growth > 128 AND Growth <=131072 AND percentageGrowth = 0 THEN CAST (convert(int,round((Growth*1.000)/128.000,0)) AS NVARCHAR (256)) + ' MB' --CAST (CAST(Size as INT) AS NVARCHAR(256)) + ' kb' WHEN Growth > 131072 AND percentageGrowth = 0 THEN CAST (convert(int,round((Growth*1.000)/(128.000*1024),0)) AS NVARCHAR (256)) + ' GB'
WHEN percentageGrowth = 1 THEN CAST (Growth as nvarchar (256)) + '%'
END
,'FileSize' =
CASE
WHEN Size <= 128 THEN CAST (convert(int,round(Size*8.000,0)) AS NVARCHAR (256)) + ' KB' WHEN Size > 128 AND SIZE <=131072 THEN CAST (convert(int,round((Size*1.000)/128.000,0)) AS NVARCHAR (256)) + ' MB' --CAST (CAST(Size as INT) AS NVARCHAR(256)) + ' kb' WHEN Size > 131072 THEN CAST (convert(int,round((Size*1.000)/(128.000*1024),0)) AS NVARCHAR (256)) + ' GB'
END
, 'Maximum File Size' =
CASE
WHEN MaxSize = -1 THEN 'NO MAX SIZE SET!'
WHEN MaxSize <= 128 THEN CAST (convert(int,round(MaxSize*8.000,0)) AS NVARCHAR (256)) + ' KB' WHEN MaxSize > 128 AND MaxSize <=131072 THEN CAST (convert(int,round((MaxSize*1.000)/128.000,0)) AS NVARCHAR (256)) + ' MB' --CAST (CAST(Size as INT) AS NVARCHAR(256)) + ' kb' WHEN MaxSize > 131072 THEN CAST (convert(int,round((MaxSize*1.000)/(128.000*1024),0)) AS NVARCHAR (256)) + ' GB'
END
, 'File State' =
CASE
WHEN m.[state] =    0 THEN 'ONLINE'
WHEN m.[state] =    1 THEN 'RESTORING'
WHEN m.[state] =    2 THEN 'RECOVERING'
WHEN m.[state] =    3 THEN 'RECOVERY_PENDING'
WHEN m.[state] =    4 THEN 'SUSPECT'
WHEN m.[state] =    5 THEN 'Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.'
WHEN m.[state] =    6 THEN 'OFFLINE'
WHEN m.[state] =    7 THEN 'DEFUNCT'
END
, 'File Type' =
CASE
WHEN [type] = 0 THEN ' Rows. (Includes files of full-text catalogs that are upgraded to or created in SQL Server 2012.)'
WHEN [type] = 1 THEN ' Log'
WHEN [type] = 2 THEN ' FILESTREAM'
WHEN [type] = 3 THEN ' Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.'
WHEN [type] = 4 THEN ' Full-text (Full-text catalogs earlier than SQL Server 2012; full-text catalogs that are upgraded to or created in SQL Server 2012 will report a file type 0.)'
END
from MasterFiles M
INNER JOIN sys.databases db
ON db.database_id = M.[dbId]

Author: Richie Lee

Full time computer guy, part time runner. Full time Dad, part time blogger. Pokémon Nut. Writer of fractured sentences. Maker of the best damn macaroni cheese you've ever tasted.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s