June 15, 2007 at 3:23 pm
Hi all!
I am interested in changing the filegrowth property of both my mdf and ldf on several hundred live databases and I was curious if anyone knows if there is a sp that can do this with out requiring a restart of the server or putting the db into single user mode? I know there is the option of doing an
alter database
modify file(name = N'<data filename>', filegrowth = <x> MB)
but this would need to be done in single user mode which means that I can only do this on a reboot night and when you are working with that many db's, it would take too long. If anyone has any insight on this, it would be helpful.
Thanks
Darron Tavish
June 15, 2007 at 3:57 pm
you don't need to reboot to do that. Yo can use the rude way to set it up in single user.
ALTER DATABASE dbName
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE dbName
MOFIFIY FILE ( NAME ='xyz', FILEGROWTH = 123 MB )
GO
ALTER DATABASE dbName
SET MULTI_USER
GO
* Noel
June 15, 2007 at 4:52 pm
Why do you think that changing the filegrowth specification requires the database to be in single user mode ?
Single user mode is only required when changing the collation.
P.S.
Do not change the filegrowth for tempdb - there is a bug !!!
SQL = Scarcely Qualifies as a Language
June 19, 2007 at 11:42 am
A database does not need to be in single user mode to modify size, growth or it's growth limiting factor. As for an SP, well you would have to write it yourself. As for me I would not automate it too much because a poorly/erroneously coded SP to do database sizing 'enmass' could potentially have disastrous results.
However I would probably script groups of say 10 at a time and execute them after triple checking everything.
Carl, could you expound on the tempdb bug please ?
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
June 20, 2007 at 9:02 am
"could you expound on the tempdb bug please"
When you change the file growth value for the tempdb database file to a percentage, the file growth is in KB not a %
e.g. if you change to 10%, it is actually changed to 10 KB.
See:
BUG: File growth value for the tempdb database is not persistent when changed from fixed increments to percentage
http://support.microsoft.com/kb/816939
SQL = Scarcely Qualifies as a Language
June 20, 2007 at 9:08 am
Thanks Carl !
... yet another reason not to autogrow by 10% ...
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
October 20, 2014 at 2:52 am
Use this script,
copy-paste-execute the output....
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
October 21, 2014 at 8:34 am
Nice looking script. Looking at this post from 7 years ago makes me realize I have come a long way! This was an issue with sql server 2000 if I remember correctly that the auto-growth settings were not accepted until the instance was restarted (on the instance level or the server level) and that was fixed with a service pack update that was installed later on anyways. Thankfully we are on pretty much a 2008R2 environment with one or two stragglers of 2005 and we are long past some of these woes. Thanks for the reply!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply