By Jesse Roberge - YeshuaAgapao@Yahoo.com
Grows all of the data files in a database based on the greater of a fixed minimum free space amount or a dynamic percentage of the size of the largest table in the filegroup.
Ensures that plenty of space is available for maintenance operations and minimizes auto-growth of data files during business hours, especially if instant file initialization (applicable to SQL 2005+) is not on.
Can optionally override max autogrow settings and also optionally temporarily set a read-only filegroup to readwrite for growth, and then revert back to readonly.
Run this in an overnight job, even if instant file initialization is on for SQL2005+, before any maintenance jobs, especially index defragmentation.
This will work with SQL Server 2005 and 2008.
Known weakness: If you have multiple files in a filegroup, it will expect the greater of MinFreeSpace or the entire percentage of the Largest Table available in each of the files of the filegroup.
This is designed to run from a central administtrative database.
This report-only version does not run the ALTER database commands. It returns a result-set with various file size and usage stats and the generated change command.
Required Input Parameters:
Optional Input Parameters:
@DatabaseID int=0, ID of the database to grow the files in. If omitted, the ID will be looked up from the name. If both ID and name are provided, the ID takes precedence.
@DatabaseName sysname='', Name of the database to grow the files in. If omitted, the name will be looked up from the ID. If both ID and name are omitted, the ID/Name of the current database is used.
@LargestTablePercent int=150, The desired amount of free space as a percentage of the largest table in the file - if the largest table is 256MB and @LargestTablePercent is 200, then it will seek to have 512MB free in the file.
@MinFreeSpace int=64, The fixed minimum free space in megabytes desired in the file, regardless of the size of the largest table.
Set to 0 if you want the largest table size to be the sole controlling factor in file growth.
@Granularity int=16, If the desired size this proc wants to grow to is 920MB and @Granularity=256 (megabytes), then it will round up to the nearest full multple of 256MB -- 1024 MB.
@OverrideMaxSize bit=0, If 1, and the desired size exceeds the maximum autogrow size, it will raise the maximum autogrow size to the size that it wants the file to grow to. Otherwise it won't touch the file.
@OverrideReadOnly bit=0, Allows growing or shrinking of files inside of READ_ONLY filegroups.
@AllowShrink bit=0, If the file is over-size, allow a DBCC ShrinkFile to be issued. Recommend to be false as it will fragment the indexes and tables in the database files.
Re-growth will also more likely to result in more file fragments at the filesystem level. But sometimes files can be grossly oversized with 90+% free space.
@ShrinkTruncateOnly bit=1 If @AllowShrink=1 and it wants to shrink a file, this will cause it not to move data pages.
Will not fragment tables and indexes, but it will very likely not shrink down to the desired size (LargestTable*@LargestTablePercent/100; round up to nearest whole mulitiple of @Granularity)
EXECUTE Admin.Util_BatchAutoGrow_ReportOnly @DatabaseName='CalvaryHelps', @LargestTablePercent=150, @MinFreeSpace=256, @Granularity=64, @AllowShrink=0, @ShrinkTruncateOnly=0
EXECUTE Admin.Util_BatchAutoGrow_ReportOnly @DatabaseName='Admin', @LargestTablePercent=200, @MinFreeSpace=4, @Granularity=16, @AllowShrink=1, @ShrinkTruncateOnly=1
This program is free software: you can redistribute it and/or modify
it under the terms of the GNU Lesser General Public License as
published by the Free Software Foundation, either version 3 of the
License, or (at your option) any later version.
This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU Lesser General Public License for more details.
see <http://www.fsf.org/licensing/licenses/lgpl.html> for the license text.