SQL Server – Script to Identify and Update Database Auto-Growth Setting

« SQL Server – Script to Configure TempDB Files per Number of Logical Processors

SQL Server – Aborted Online Index Operation & Observed Elevated Values for PWAIT_MD_RELATION_CACHE and MD_LAZYCACHE_RWLOCK Wait Types »

SQL Server – Script to Identify and Update Database Auto-Growth Setting

June 25, 2014 by Hareesh G

An auto-growth event is the process by which the SQL Server engine expands the size of a database file when it runs out of space. The amount by which a database file should grow is determined by the value that is set in the SQL configuration. While every organization has its own standards for this setting, however there are some widely followed best practices like for instance the auto-growth setting should not be in percent value

In this post I have put down two scripts that you can customize per your organizational standards. The first script helps you to identify the current settings and the second one can be used to update auto-growth settings. Both the scripts are targeted for all the databases by default, however you can call out the databases that you want to exclude from this change. Please make sure that you test these changes before pushing out to production servers.

Script 1: Identify the current database settings

select DB_NAME(mf.database_id) database_name
, mf.name logical_name
, CONVERT (DECIMAL (20,2) , (CONVERT(DECIMAL, size)/128)) [file_size_MB]
, CASE mf.is_percent_growth
WHEN 1 THEN 'Yes'
ELSE 'No'
END AS [is_percent_growth]
, CASE mf.is_percent_growth
WHEN 1 THEN CONVERT(VARCHAR, mf.growth) + '%'
WHEN 0 THEN CONVERT(VARCHAR, mf.growth/128) + ' MB'
END AS [growth_in_increment_of]
, CASE mf.is_percent_growth
WHEN 1 THEN
CONVERT(DECIMAL(20,2), (((CONVERT(DECIMAL, size)*growth)/100)*8)/1024)
WHEN 0 THEN
CONVERT(DECIMAL(20,2), (CONVERT(DECIMAL, growth)/128))
END AS [next_auto_growth_size_MB]
, CASE mf.max_size
WHEN 0 THEN 'No growth is allowed'
WHEN -1 THEN 'File will grow until the disk is full'
ELSE CONVERT(VARCHAR, mf.max_size)
END AS [max_size]
, physical_name
from sys.master_files mf

Script 2: Update database auto-growth values

/*
****MODIFICATION REQUIRED for AUTOGROWTH -- See line 64 below****
1) Use this script to change the auto growth setting of
   for all databases
2) If you want to exclude any database add the DBs in the
   WHERE Clause -- See line 50 below
3) Tested in 2012 and 2014 SQL Servers
*/

IF EXISTS(SELECT name FROM sys.sysobjects WHERE name = N'ConfigAutoGrowth' AND xtype='U')
DROP TABLE ConfigAutoGrowth
GO
CREATE TABLE DBO.ConfigAutoGrowth
(
iDBID INT,
sDBName SYSNAME,
vFileName VARCHAR(max),
vGrowthOption VARCHAR(12)
)
PRINT 'Table ConfigAutoGrowth Created'
GO
-- Inserting data into staging table
INSERT INTO DBO.ConfigAutoGrowth
SELECT
SD.database_id,
SD.name,
SF.name,
CASE SF.status
WHEN 1048576 THEN 'Percentage'
WHEN 0 THEN 'MB'
END AS 'GROWTH Option'
FROM SYS.SYSALTFILES SF
JOIN
SYS.DATABASES SD
ON
SD.database_id = SF.dbid
GO

-- Dynamically alters the file to set auto growth option to fixed mb
DECLARE @name VARCHAR ( max ) -- Database Name
DECLARE @dbid INT -- DBID
DECLARE @vFileName VARCHAR ( max ) -- Logical file name
DECLARE @vGrowthOption VARCHAR ( max ) -- Growth option
DECLARE @Query VARCHAR(max) -- Variable to store dynamic sql

DECLARE db_cursor CURSOR FOR
SELECT
idbid,sdbname,vfilename,vgrowthoption
FROM configautogrowth
--WHERE sdbname NOT IN ( 'master' ,'msdb' ) --<<--ADD DBs TO EXCLUDE
--AND vGrowthOption = 'Percentage' or 'Mb'

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbid,@name,@vfilename,@vgrowthoption
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Changing AutoGrowth option for database:- '+ UPPER(@name)

/******If you want to change the auto growth size to a different 
value then just modify the filegrowth value in script below *********/

SET @Query = 'ALTER DATABASE '+ '[' + @name + ']' +'
MODIFY FILE (NAME = '+ '[' +@vFileName + ']' +',FILEGROWTH = 5MB)' --<<--ADD AUTOGROWTH SIZE HERE
EXECUTE(@Query)

FETCH NEXT FROM db_cursor INTO @dbid,@name,@vfilename,@vgrowthoption
END
CLOSE db_cursor -- Closing the curson
DEALLOCATE db_cursor -- deallocating the cursor

GO
-- Querying system views to see if the changes are applied
DECLARE @SQL VARCHAR(8000), @sname VARCHAR(3)
SET @SQL=' USE [?]
SELECT ''?'' [Dbname]
,[name] [Filename]
,CASE is_percent_growth
WHEN 1 THEN CONVERT(VARCHAR(5),growth)+''%''
ELSE CONVERT(VARCHAR(20),(growth/128))+'' MB''
END [Autogrow_Value]
,CASE max_size
WHEN -1 THEN CASE growth
WHEN 0 THEN CONVERT(VARCHAR(30),''Restricted'')
ELSE CONVERT(VARCHAR(30),''Unlimited'') END
ELSE CONVERT(VARCHAR(25),max_size/128)
END [Max_Size]
FROM [?].sys.database_files'

IF EXISTS(SELECT 1 FROM tempdb..sysobjects WHERE name='##Fdetails')
DROP TABLE ##Fdetails
CREATE TABLE ##Fdetails
(Dbname VARCHAR(50),Filename VARCHAR(50),
Autogrow_Value VARCHAR(15),Max_Size VARCHAR(30))
INSERT INTO ##Fdetails
EXEC sp_msforeachdb @SQL
SELECT * FROM ##Fdetails ORDER BY Dbname

--Dropping the staging table
DROP TABLE ConfigAutoGrowth
GO

Posted in T-SQL | Tagged SQL Server 2008, SQL Server 2008 R2, SQL Server 2012 | 19 Comments

19 Responses

on July 22, 2014 at 1:44 am | Reply Brett

Great script. Works for me. Thanks for putting these together
on February 10, 2015 at 12:01 am | Reply Sql Server Auto Growth | Autommy

[…] SQL Server – Script to Identify and Update Database Auto … – An auto-growth event is the process by which the SQL Server engine expands the size of a database file when it runs out of space. The amount by which a database file …… […]
on April 9, 2015 at 11:42 am | Reply Andy C

I’m receiving:
Msg 102, Level 15, State 1, Line 8
Incorrect syntax near ‘;’.

Assuming here: CASE SF.status & 0x100000
on May 7, 2015 at 8:03 am | Reply Melanie

I am also getting the same error on statement ‘CASE SF.status & 0x100000’
on May 7, 2015 at 10:26 am | Reply Hareesh Gottipati

I have updated the script and now works good. Sorry about that, a formatting issue with the code editor.
on June 25, 2015 at 2:37 pm | Reply Becky

Great script! Thanks! I did notice that different numbers for SF.status came up for my server (newer version of sql?). No matter… I changed your first query to load the file to see what numbers I was getting and double checked it to the actual growth type through the gui, so basically I only ran to before updating to the new settings. The else makes sure that I can see if I have caught all the values now. Also run only to just before:

— Dynamically alters the file to set auto growth option to fixed mb

My version:
— Inserting data into staging table
INSERT INTO DBO.ConfigAutoGrowth
SELECT
SD.database_id,
SD.name,
SF.name,
CASE SF.status
WHEN 1048576 THEN ‘Percentage’
WHEN 1048578 THEN ‘Percentage’
WHEN 1048642 THEN ‘Percentage’
WHEN 2 THEN ‘MB’
ELSE CONVERT(varchar(10), SF.status)
END AS vGrowthOption
FROM SYS.SYSALTFILES SF
JOIN SYS.DATABASES SD ON SD.database_id = SF.dbid
GO

Then I added a select to see the results:

select * from DBO.ConfigAutoGrowth

I also ran the last part first to check what the settings were before the changes from the point:
— Querying system views to see if the changes are applied

In the select that gets the rows to update, I also added another where in order to only change data files and treat the logs differently:

and vFileName not like ‘%_log’ — leave logs as percentage and fix differently

This is awesome and exactly what I needed. (well except for the changes!) Thanks!
on July 16, 2015 at 3:37 pm | Reply Hareesh Gottipati

Thanks for pointing that out Becky. I haven’t found much information on the status column, may be it is different for versions.

Have a good one!
on August 8, 2015 at 6:58 pm | Reply How to update DB File Growth Settings for ALL Databases at the same time | SQL Server Support Services | DBA Services (Australia)

[…] already… and as DBAs are all busy and important people, we’ve hunted down this cool script which largely does this bulk update of all databases’ growth settings all at […]
on December 10, 2015 at 7:00 am | Reply SQL Server – Script to Identify and Update Database Auto-Growth Setting – SQL Meal

[…] SQL Server – Script to Identify and Update Database Auto-Growth Setting […]
on January 29, 2016 at 4:07 am | Reply DrJogalog

The dynamic SQL does not cater for hyphens, and I assume other special characters, in the database name causing the SQL to fail on specific updates. Adding qualifying brackets around the database name in the script resolves this.

Amend Line 62
SET @Query = ‘ALTER DATABASE ‘+ ‘[‘ + @name + ‘]’ +’
- on June 8, 2016 at 6:07 pm | Reply Matt D
  
  Also the line after line 62 (63)
  
  MODIFY FILE (NAME = ‘+ ‘[‘ +@vFileName + ‘]’ +’,FILEGROWTH = 200MB)’
  
  This allows for databases that have spaces in the name.
  
  So line 62 and 63 should be
  
  SET @Query = ‘ALTER DATABASE ‘+ ‘[‘ + @name + ‘]’ +’
  MODIFY FILE (NAME = ‘+ ‘[‘ +@vFileName + ‘]’ +’,FILEGROWTH = 200MB)’ –<<–ADD AUTOGROWTH SIZE HERE
on June 9, 2016 at 12:08 am | Reply Hareesh Gottipati

@Jogalog and Matt D: Thanks a lot for the suggestion. Tested and updated the script as per your inputs.
on July 12, 2016 at 7:19 am | Reply Taiob M Ali

Hareesh,
Great post. I have modified version to set the auto growth size based on the current file size and excluding files that are not set to auto grow. If you want I can send a copy.
on December 18, 2016 at 11:11 pm | Reply Johnson

Great, this is so chock full of users information and the resources you provided was helpful to me. There i found one more block full of informative information about autogrowth in SQL Server:
http://www.sqlmvp.org/database-autogrowth-in-sql-server/
on February 4, 2018 at 4:45 am | Reply Scott

Reblogged this on Scott Drummond SQL Blog and commented:
This is literally one of the best scripts I’ve found. It’s a DISA CAT I finding for SQL 2008 R2/2012 if auto-growth of transaction logs is set to be a percentage. This quickly allows you to modify the auto-growth values. A very helpful script!
on April 15, 2019 at 9:16 pm | Reply Zie

Is there anyway we can have a script to add at least 100MB to Data file is the Free space is below 10MB?

Like we have this script to see Free space left in Data file in SQL:

SELECT a.NAME, a.FILENAME, [FILE_SIZE_MB] = convert(decimal(12,2), round(a.size/128.000,2)), [SPACE_USED_MB] = convert(decimal(12,2), round(fileproperty(a.name,’SpaceUsed’)/128.000,2)), [FREE_SPACE_MB] = convert(decimal(12,2), round((a.size-fileproperty(a.name,’SpaceUsed’))/128.000,2)) , [FREE_SPACE_%] = convert(decimal(12,2), (convert(decimal(12,2),round((a.size-fileproperty(a.name,’SpaceUsed’))/128.000,2)) / convert(decimal(12,2),round(a.size/128.000,2)) * 100)) FROM dbo.sysfiles a
- on April 15, 2019 at 9:22 pm | Reply Zie
  
  Hi All,
  
  Some mistake on the explanation:
  
  Is there anyway we can have a script to add (increase) at least 100MB to Data file if the Free space is below 10MB?
  
  If Data file = 9MB, Autogrowth size with additional 100MB to Data File. New size will 109MB.
  
  Like we have this script to see Free space left in Data file in SQL:
  
  SELECT a.NAME, a.FILENAME, [FILE_SIZE_MB] = convert(decimal(12,2), round(a.size/128.000,2)), [SPACE_USED_MB] = convert(decimal(12,2), round(fileproperty(a.name,’SpaceUsed’)/128.000,2)), [FREE_SPACE_MB] = convert(decimal(12,2), round((a.size-fileproperty(a.name,’SpaceUsed’))/128.000,2)) , [FREE_SPACE_%] = convert(decimal(12,2), (convert(decimal(12,2),round((a.size-fileproperty(a.name,’SpaceUsed’))/128.000,2)) / convert(decimal(12,2),round(a.size/128.000,2)) * 100)) FROM dbo.sysfiles a
  
  NAME | FILENAME | FILE_SIZE_MB | SPACE_USED_MB | FREE_SPACE_MB | FREE_SPACE_%
  DB01 | C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\Db01.mdf | 20.00 | 16.13 | 3.88 | 19.40
  DB01_log | C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\DB01_log.ldf | 1.25 | 0.60 | 0.65 | 52.00
on November 7, 2019 at 11:32 pm | Reply Sai

Very good script and helpful
on November 22, 2019 at 7:55 am | Reply David

Lovely stuff, thank you.