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
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]
sys.master_files f
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?]
#temp
WHERE [Growth Value MB] < [Growth should be MB]
DROP TABLE #temp
x
Comments
Post a Comment