Script to change auto growth settings for all databases in SQL Server

• Shashank Srivastava

  Mr or Mrs. 500

  Points: 584

  More actions

  May 21, 2013 at 3:59 pm

  #274100

  Comments posted to this topic are about the item Script to change auto growth settings for all databases in SQL Server

  Regards
  Shashank Srivastava
  MCITP - SQL SERVER 2008
  INDIA
  Follow me @ http://shashanksrivastavasqldba.blogspot.com/

• aleksey donskoy

  Mr or Mrs. 500

  Points: 565

  More actions

  May 29, 2013 at 10:15 am

  #1619381

  Good script.

  I had to change 3 things to make it work:

  1. Insted of execute (@Query) corrected it to PRINT @Query, cause I wanted to check the script before running

  2. Corrected USE ''?'' to USE [?] having to deal with the SharePoint databases with dashes in their names

  3. Change the following line:

  From:

  CREATE TABLE ##Fdetails (Dbname VARCHAR(50),Filename VARCHAR(50),Autogrow_Value VARCHAR(15),Max_Size VARCHAR(30))

  To:

  CREATE TABLE ##Fdetails (Dbname SYSNAME,Filename VARCHAR(128),Autogrow_Value VARCHAR(15),Max_Size VARCHAR(30))

  as the SherePoint databases names and filenames did not fit in.

  Looks good otherwise.

  Thanks.

  Alex Donskoy

  Greeneberg Trauriq PA

  Miami, FL

• Shashank Srivastava

  Mr or Mrs. 500

  Points: 584

  More actions

  May 29, 2013 at 10:19 am

  #1619383

  Thanks for the new addition. These are really helpful.

  Regards
  Shashank Srivastava
  MCITP - SQL SERVER 2008
  INDIA
  Follow me @ http://shashanksrivastavasqldba.blogspot.com/

• Anja

  SSC Enthusiast

  Points: 167

  More actions

  October 20, 2014 at 2:38 am

  #1753422

  Thanks for this script!

  I changed it a bit for my use:

  - using a table variable instead of a 'real' table

  - use dynamic autogrow-settings depending on the size of the db-file

  - use the sys.master_files table instead of the depricated sysaltfiles table

  - removed the cursor

  - view the script without executing it directly

  DECLARE @ConfigAutoGrowth as table

  (

  iDBID INT,

  sDBName SYSNAME,

  vFileName VARCHAR(max),

  vGrowthOption VARCHAR(12),

  vgrowth bigint ,

  vsize bigint,

  cmd varchar(max)

  )

  -- Inserting data into staging table

  INSERT INTO @ConfigAutoGrowth

  SELECT

  SD.database_id,

  SD.name,

  SF.name,

  CASE is_percent_growth

  WHEN 1

  THEN 'Percentage'

  WHEN 0 THEN 'MB'

  END AS 'GROWTH Option',

  case when is_percent_growth =1 then growth else growth*8/1024 end,

  size *8/1024 ,

  ''

  FROM sys.master_files SF

  INNER JOIN

  SYS.DATABASES SD

  ON

  SD.database_id = SF.database_id

  --Change value increments, non-percentage growths

  UPDATE @configautogrowth

  SET cmd =

  CASE

  WHEN vsize < 300 AND vGrowth <> 50 THEN 'ALTER DATABASE '+ sDBName +' MODIFY FILE (NAME = '+vFileName+',FILEGROWTH = 50MB)'

  WHEN vsize BETWEEN 300 and 1000 AND vGrowth <> 100 THEN 'ALTER DATABASE '+ sDBName +' MODIFY FILE (NAME = '+vFileName+',FILEGROWTH = 100MB)'

  WHEN vsize BETWEEN 1000 and 2000 AND vGrowth <> 200 THEN 'ALTER DATABASE '+ sDBName +' MODIFY FILE (NAME = '+vFileName+',FILEGROWTH = 200MB)'

  WHEN vsize > 2000 AND vGrowth <> 400 THEN 'ALTER DATABASE '+ sDBName +' MODIFY FILE (NAME = '+vFileName+',FILEGROWTH = 400MB)'

  END

  WHERE 1=1

  --and sdbname NOT IN ( 'master' ,'msdb' )

  AND vGrowthOption ='MB'

  -- Change percentage-growths

  UPDATE @configautogrowth

  SET cmd =

  CASE

  WHEN vsize < 300 THEN 'ALTER DATABASE '+ sDBName +' MODIFY FILE (NAME = '+vFileName+',FILEGROWTH = 50MB)'

  WHEN vsize BETWEEN 300 and 1000 THEN 'ALTER DATABASE '+ sDBName +' MODIFY FILE (NAME = '+vFileName+',FILEGROWTH = 100MB)'

  WHEN vsize BETWEEN 1000 and 2000 THEN 'ALTER DATABASE '+ sDBName +' MODIFY FILE (NAME = '+vFileName+',FILEGROWTH = 200MB)'

  WHEN vsize > 2000 THEN 'ALTER DATABASE '+ sDBName +' MODIFY FILE (NAME = '+vFileName+',FILEGROWTH = 400MB)'

  END

  WHERE 1=1

  AND vGrowthOption ='Percentage'

  --show ALTER-statements

  SELECT cmd FROM @configautogrowth

  where cmd is not null

  GO

  I hope this will help someone.....

• Elizabeth.Block

  Hall of Fame

  Points: 3516

  More actions

  April 6, 2015 at 5:12 pm

  #1789076

  Anja, your script is really helpful! I'm working on fixing the autogrowth settings on our servers and your script is very helpful. It's a good script because it's using current system views instead of deprecated ones.

  Thanks!

  Elizabeth

• akljfhnlaflkj

  SSC Guru

  Points: 76202

  More actions

  May 11, 2015 at 11:40 am

  #1797050

  Thanks for the script.