Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

The Automated DBA: Batch Database File Grower - SQL2005 ReportOnly Ver

By Jesse Roberge,

*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=
Util_BatchAutoGrow_ReportOnly
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:
none

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)

Usage:
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

Copyright:
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.

*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=

Total article views: 1009 | Views in the last 30 days: 2
 
Related Articles
FORUM

Restoring database - Filegroups

Restoring database - Filegroups

SCRIPT

The Automated DBA: Batch Database File Grower - SQL2000 ReportOnly Ver

Grows all of the data files in a database based on the greater of a fixed minimum free space amount ...

SCRIPT

The Automated DBA: Batch Database File Grower - SQL 2000 Version

Grows all of the data files in a database based on the greater of a fixed minimum free space amount ...

SCRIPT

The Automated DBA: Batch Database File Grower - SQL 2005 Version

Grows all of the data files in a database based on the greater of a fixed minimum free space amount ...

BLOG

Find the largest tables in a database (II)

I once wrote a post to find the largest tables in a database, and now after studying PowerShell, I f...

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones