Check Database files autogrowth

Check Database files autogrowth



SELECT
DB_name(S.database_id) AS [Database Name]
,S.[name] AS [Logical Name]
,S.[file_id] AS [File ID]
, S.[physical_name] AS [File Name]
, Left(S.[physical_name], 1) as Drive
,CAST(CAST(G.name AS VARBINARY(256)) AS sysname) AS [FileGroup_Name]
,CONVERT (varchar(10),(S.[size]*8)) + ' KB' AS [Size]


,CASE WHEN S.[max_size]=-1 THEN 'Unlimited' ELSE CONVERT(VARCHAR(10),CONVERT(bigint,S.[max_size])/128)  END AS [Max Size MB]
,CASE s.is_percent_growth WHEN 1 THEN CONVERT(VARCHAR(10),S.growth) +'%' ELSE Convert(VARCHAR(10),S.growth/128) END AS [Growth MB]




,Case WHEN S.[type]=0 THEN 'Data Only'
WHEN S.[type]=1 THEN 'Log Only'
WHEN S.[type]=2 THEN 'FILESTREAM Only'
WHEN S.[type]=3 THEN 'Informational purposes Only'
WHEN S.[type]=4 THEN 'Full-text '
END AS [usage]

   --S.is_media_read_only,
   --S.is_read_only,
--   S.is_sparse
   --S.is_percent_growth,
   --S.is_name_reserved,
   --S.create_lsn,
   --S.drop_lsn,
   --S.read_only_lsn,
   --S.read_write_lsn,
   --S.differential_base_lsn,
   --S.differential_base_guid,
   --S.differential_base_time,
   --S.redo_start_lsn,
   --S.redo_start_fork_guid,
   --S.redo_target_lsn,
   --S.redo_target_fork_guid,
   --S.backup_lsn

FROM sys.master_files AS S
LEFT JOIN sys.filegroups AS G ON ((S.type = 2 OR S.type = 0)
AND (S.drop_lsn IS NULL)) AND (S.data_space_id=G.data_space_id)
where database_id > 4 and DB_name(S.database_id) not in ('dbadmin', 'sysadmin')