Autogrowth settings
Job details
| Name: | Autogrowth settings | |
| Platform: | Sqlserver | |
| Category: | Maintenance | |
| Description: | Checks database files auto-growth settings. | |
| Long description: | The default auto-grow settings associated with the model database are not the best settings for how databases grows. If you haven’t been diligent at setting the auto-grow parameters when you created databases then you might want scan your instance to determine which databases are using the default setting. We have split the possible file sizes and growth settings into 4 ranges. The first range, which we do not analyze, are files smaller than ‘minimum file size STEP 1′. The second rage, files between ‘minimum file size STEP 1′ and ‘minimum file size STEP 2′, are verified to have file growth settings equal ‘minimum growth size STEP 1′. The third range, files between ‘minimum file size STEP 2′ and ‘minimum file size STEP 3′, are verified to have file growth settings equal ‘minimum growth size STEP 2′. The last rage verified that all files (bigger than ‘minimum file size STEP 3′) have minimum growth settings equal or higher than ‘minimum growth size STEP 3′ and at the same time lower than ‘maximum growth size STEP 3′. Only ONLINE databases with the following properties are included: UserAccess = MULTI_USER, Updateability = READ_WRITE, IsInStandBy = 0, IsMergePublished = 0, IsPublished = 0 and IsSubscribed = 0. | |
| Version: | 2.2 | |
| Default schedule: | 15 15 1 * | |
| Requires engine install: | Yes | |
| Compatibility tag: | .[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version > ‘2005′ & hasengine=‘YES’ & eng_inst_priv = 0 & (engine_edition = ‘Microsoft SQL Server’ | engine_edition = ‘Azure SQL Managed Instance’)] |
Parameters
| Name | Default value | Description |
|---|---|---|
| minimum file size STEP 1 | 100 | The minimum size (in MB) for data and transaction log files to be checked. Files with size between “minimum file size STEP 1″ and “minimum file size STEP 2″ will be checked for file growth configuration. |
| minimum growth size STEP 1 | 10 | The minimum growth size (in MB) for database files with size in rage between “minimum file size STEP 1″ and “minimum file size STEP 2″ parameter values. |
| minimum file size STEP 2 | 1000 | The minimum size (in MB) for data and transaction log files to be checked. Files with size between “minimum file size STEP 2″ and “minimum file size STEP 3″ will be checked for file growth configuration. |
| minimum growth size STEP 2 | 100 | The minimum growth size (in MB) for database files with size in rage between “minimum file size STEP 2″ and “minimum file size STEP 3″ parameter values. |
| minimum file size STEP 3 | 10000 | The minimum size (in MB) for data and transaction log files to be checked. Files with size bigger than “minimum file size STEP 3″ will be checked for file growth configuration. |
| minimum growth size STEP 3 | 500 | The minimum growth size (in MB) for database files with size bigger than “minimum file size STEP 3″ parameter values. |
| maximum growth size STEP 3 | 5000 | The maximum growth size (in MB) for files larger than “minimum file size STEP 3″ parameter value. |
| return status when grow by percentage | 1 | Return status value (ALARM – 2, WARNING – 1, or OK – 0) when grow by a specific percentage. |
| return status when growth size too small | 1 | Return status value (ALARM – 2, WARNING – 1, or OK – 0) when grow by a specific size is too small (ex. less than parameters values “minimum growth size STEP 1-3″). |
| return status when growth size too big | 1 | Return status value (ALARM – 2, WARNING – 1, or OK – 0) when grow by a specific size is too big (ex. higher than parameters values “minimum growth size STEP 2-3″ or parameter “maximum growth size STEP 3″). |
| change growth settings | NO | If set to “YES“ the alert will change the growth settings according to all STEP values. |
| ignore status when change settings is on | YES | Return status OK when parameter “change growth settings” is set to “YES“ |
| ignore databases | tempdb | List of databases (separated by comma) which will be ignored. You can use % (percent sign) to represent wild card characters. |
Job Summary
The dbWatch monitoring job, specifically designed for the dbWatch Control Center, focuses on evaluating and adjusting the auto-growth settings of database files across different SQL Server instances.
- Purpose: The primary goal of this job is to evaluate the auto-growth settings of database files, ensuring they are optimized based on the file size and to prevent performance degradation due to frequent auto-sizing.
- Why: It is crucial in database management to ensure that auto-growth settings are neither too small to cause frequent auto-grow operations, nor too large to use excessive disk space ineffectively. This job helps maintain the balance by auditing and adjusting these settings to facilitate optimal performance and resource utilization.
- Manual Checking: While the specific SQL commands used by the job are not provided, generally, one could manually check auto-growth settings in SQL Server using the following query:
EXEC sp_helpfile;
This command provides information about the current size and growth settings of the database files.Operation Details
The job operates by defining various size thresholds and corresponding growth settings for database files. It checks these settings against predefined optimal values and adjusts them if necessary.
- Operation: It includes scanning database files of different size ranges and verifying if their growth settings are aligned with the recommended settings. If discrepancies are found based on the specified range conditions, changes are suggested or automatically applied depending on the configuration.
- Databases Included: Only online databases configured for multi-user access and read/write operations are included for checks. Databases like ‘tempdb’, ‘master’, etc., are typically excluded alongside any databases specifically listed to be ignored in the job’s parameters.
- Scheduling: The job runs as per a configured schedule which is suggested to be at least once a month by default but can be adjusted to run more frequently depending on the environment’s needs.
Technical Implementation
The task is implemented through a series of SQL procedures and temporary tables that help in gathering data, executing analysis, and applying changes if necessary.
- Main Components:
- SQL scripts and procedures to fetch current settings, evaluate them against best practices, and modify settings where required.
Reporting
This job provides detailed reports on auto-growth settings for database files, summarizing configurations across the server and indicating whether any settings are outside the recommended thresholds.
- Report Features: Including data about growth configurations, file types, size distributions, and specific cases where growth settings do not align with recommended practices.
- Customization: The output can be customized to include more specific details or to adjust the presentation style depending on administrative needs.
Conclusion
By continuously monitoring and adjusting database file growth settings, this dbWatch job plays a critical role in maintaining database performance and storage efficiency. The automated checks and potential adjustments help database administrators prevent common issues related to suboptimal auto-growth configurations.