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

Question

Post reply

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/

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

aleksey donskoy Mr or Mrs. 500 Points: 565 More actions · Answer 1

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 · Answer 2

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: 166 More actions · Answer 3

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 · Answer 4

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 · Answer 5

akljfhnlaflkj

SSC Guru

Points: 76202

May 11, 2015 at 11:40 am

#1797050

Thanks for the script.