This is the logical conclusion of my reset_tempdb and reset_model scripts. It show all of the file sizes and autogrowth settings in the current instance and the code to change them.

The suggested sizes (128 MB for Logfiles and 256 MB for Datafiles) are reasonable for Model, but should probably be amended for other databases dependent on current size and autogrowth history.

--autogrowth_all.sql

-- get current settings & create commands to change them
select	db.Name, case mf.[Type] when 0 then 'DATA' else 'LOG' end [FileType],
	convert(varchar(50), size*8/1024) + ' MB' [CurrentSize], 
	case mf.is_percent_growth 
		when 1 then convert(varchar(50), growth) + ' %' 
		when 0 then convert(varchar(50), growth*8/1024) + ' MB' end [AutoGrowth],
	'ALTER DATABASE [' + db.Name + '] MODIFY FILE (NAME = N''' + mf.Name + ''', SIZE = ' +        case mf.[type] when 0 then '256' else '128' end + 'MB);' [ReSizeCommand],
	'ALTER DATABASE [' + db.Name + '] MODIFY FILE (NAME = N''' + mf.Name + ''', FILEGROWTH = ' +  case mf.[type] when 0 then '256' else '128' end + 'MB);' [AutogrowthCommand]
from [master].[sys].[master_files] mf
join [master].[sys].[databases] db 
on mf.database_id = db.database_id
order by mf.database_id, mf.[type];
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s