Set Auto growth for all the databases wit T-SQL

Introduction

Managing the growth of database files is an essential aspect of maintaining a healthy SQL Server environment. As databases grow over time, it becomes crucial to configure appropriate growth settings to ensure optimal performance and prevent storage-related issues. In this blog post, we will explore a T-SQL script that simplifies the process of generating commands to modify the growth settings for database files in SQL Server.

SQL Script for database Auto Growth

/*-------------------------------------------------------------------------
-- Script: T-SQL Script to Generate Database Growth Commands
-- Author: SOYELUDDIN BISWAS
-- Date: 24/09/2019
-- Email: st.biswas99@gmail.com
-- Description: 
-- This script generates commands to modify the growth settings for database files in SQL Server.
-- The growth type and value can be customized using the @GrowthType variable.
-- The script creates a temporary table to store the commands and displays the results.
-------------------------------------------------------------------------*/

-- Set the database context to [tempdb]
USE [tempdb]
GO

-- Drop the temporary table if it already exists
IF OBJECT_ID('tempdb..#DBGrowth') IS NOT NULL
    DROP TABLE #DBGrowth

-- Declare variables
DECLARE @GrowthType NVARCHAR(10)
DECLARE @DatabaseName NVARCHAR(300)
DECLARE @logicalName NVARCHAR(100) -- Increased the length to 100 to accommodate longer logical names
DECLARE @GrowthCMD NVARCHAR(300)

-- Set the growth type and value
-- Example: If you want to set growth in percentage, mention the value with '%' such as @GrowthType = '10%'
--          If you want to set growth in MB, mention the value in KB such as @GrowthType = '1024KB'
-- Copy the desired growth type below
SET @GrowthType = '1024KB'

-- Create a temporary table to store the growth commands
CREATE TABLE #DBGrowth
(
    DatabaseName NVARCHAR(300),
    logicalName NVARCHAR(100), -- Increased the length to 100 to accommodate longer logical names
    GrowthCMD NVARCHAR(300)
)

-- Insert growth commands into the temporary table
INSERT INTO #DBGrowth
SELECT DBS.name,
       DBF.name,
       'ALTER DATABASE [' + DBS.name + '] MODIFY FILE (NAME = N''' + DBF.name + ''', MAXSIZE = UNLIMITED, FILEGROWTH = ' + @GrowthType + ')'
FROM sys.databases DBS
INNER JOIN sys.sysaltfiles DBF ON DBS.database_id = dbf.dbid
WHERE DBS.name NOT IN ('master', 'tempdb', 'model', 'msdb')

-- Display the generated commands
SELECT DatabaseName AS [Database Name],
       logicalName AS [Logical Name],
       GrowthCMD AS [Command to execute]
FROM #DBGrowth