SCRIPT: How to check all database files configuration?

SELECT
          d.name,
       f.name AS [File Name],
       CONVERT(BIGINT, f.size)* 8 / 1024 AS [Database Size MB],
       f.growth * 8 / 1024 AS [Growth Value MB],
          CASE
                       WHEN CONVERT(BIGINT, f.size)* 8 / 1024 < 2000 THEN '128' -- 128MB
                       WHEN CONVERT(BIGINT, f.size)* 8 / 1024 > 2000 AND CONVERT(BIGINT, f.size)* 8 / 1024 < 5000 THEN '256' -- 256MB
                       WHEN CONVERT(BIGINT, f.size)* 8 / 1024 > 5000 AND CONVERT(BIGINT, f.size)* 8 / 1024 < 10000 THEN '512' -- 512MB
                       WHEN CONVERT(BIGINT, f.size)* 8 / 1024 > 10000 THEN '1024' -- 1024MB
          END AS [Growth should be MB],
          CASE
                       WHEN CONVERT(BIGINT, f.size)* 8 / 1024 < 2000 THEN 'ALTER DATABASE [' + DB_NAME(f.database_id) + '] MODIFY FILE ( NAME = N''' + f.name + ''', FILEGROWTH = 128MB );' -- 128MB
                       WHEN CONVERT(BIGINT, f.size)* 8 / 1024 > 2000 AND CONVERT(BIGINT, f.size)* 8 / 1024 < 5000 THEN 'ALTER DATABASE [' + DB_NAME(f.database_id) + '] MODIFY FILE ( NAME = N''' + f.name + ''', FILEGROWTH = 256MB );' -- 256MB
                       WHEN CONVERT(BIGINT, f.size)* 8 / 1024 > 5000 AND CONVERT(BIGINT, f.size)* 8 / 1024 < 10000 THEN 'ALTER DATABASE [' + DB_NAME(f.database_id) + '] MODIFY FILE ( NAME = N''' + f.name + ''', FILEGROWTH = 512MB );' -- 512MB
                       WHEN CONVERT(BIGINT, f.size)* 8 / 1024 > 10000 THEN 'ALTER DATABASE [' + DB_NAME(f.database_id) + '] MODIFY FILE ( NAME = N''' + f.name + ''', FILEGROWTH = 1024MB );' -- 1024MB
          END AS [SCRIPT],
       CASE
              WHEN f.is_percent_growth = 1 THEN 'Percentage Growth'
              ELSE 'MB Growth'
       END AS [Growth Type]
INTO #temp
FROM
       sys.master_files f
          INNER JOIN sys.databases d
          ON f.database_id = d.database_id
WHERE
          DB_NAME(f.database_id) <> 'tempdb'
          AND d.state_desc = 'ONLINE'
          AND f.type_desc <> 'FILESTREAM'
ORDER BY d.name
 
SELECT
        *,
       CASE
              WHEN [Growth Value MB] >= [Growth should be MB] THEN 'GOOD'
              ELSE 'NOT GOOD'
       END AS [Good or not?]
FROM
       #temp
WHERE [Growth Value MB] < [Growth should be MB]
 
DROP TABLE #temp
x

Comments