Autoshrink settings
Job details
| Name: | Autoshrink settings | |
| Platform: | Sqlserver | |
| Category: | Maintenance | |
| Description: | Checks if all databases have auto-shrink turned off. | |
| Long description: | Checks if all databases have auto-shrink turned off. 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: | 1.5 | |
| Default schedule: | 0 7 7 * | |
| 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 |
|---|---|---|
| ignore databases | List of databases (separated by comma) which will be ignored. You can use % (percent sign) to represent wild card characters. | |
| return status | 1 | Return status (ALARM – 2, WARNING – 1, or OK – 0) when Auto shrink is turned ON. |
| turn OFF auto shrink | NO | If set to “YES“ the alert will turn OFF the auto shrink settings. |
Job Summary: Autoshrink Settings Monitoring
- Purpose: The purpose of this monitoring job is to ensure that the auto-shrink setting is turned off for all online, multi-user, read-write databases in a SQL Server or Azure SQL Managed Instance environment.
- Why: Auto-shrink can have a detrimental impact on database performance due to frequent, often unnecessary data file size reductions, which can lead to physical file fragmentation. Ensuring it remains disabled prevents unexpected performance degradation. This job actively reports and optionally modifies these settings to align with best practices.
- Manual Checking: To verify auto-shrink settings manually, execute the following SQL command:
SELECT name, is_auto_shrink_on FROM sys.databases WHERE is_auto_shrink_on = 1;Implementation Details
- This SQL Server only job runs a stored procedure that checks each online database (excluding any databases specifically ignored via parameters) to see if the auto-shrink setting is enabled.
- If auto-shrink is found to be enabled, it logs the status, and depending on configuration, may attempt to turn auto-shrink off. This action is skipper for databases involved in SQL Server Always On availability groups to avoid conflicts.
- A key feature includes handling exceptions and errors during the check, ensuring any issues are logged and appropriate status is set for monitoring alerts.
Settings and Customization
- Ignore List: Databases to ignore during checks can be specified, supporting wildcards.
- Return Status: Determines the severity level reported (ALARM, WARNING, OK) when auto-shrink is turned on.
- Auto Correction: An option provided to automatically switch off auto-shrink if it is found to be enabled.
Dependency and Execution
- This job is dependent on its own execution continuation, meaning if any step fails, subsequent steps may not execute, and cleanup will occur to revert any temporary changes.
Report Generation
This job includes a report generation phase where it lists all databases with their respective auto-shrink status.
| Database Name | Auto shrink |
|---|---|
| ExampleDB | ON |
| AnotherDB | OFF |
- The report provides a direct view of which databases might need attention if auto-shrink is enabled, aiding in quick assessments and audits by database administrators.
Scheduled and Triggered Execution
- The job is scheduled to run at a default early morning hour every day, but this can be reconfigured according to maintenance windows and operational preferences.
By effectively monitoring and managing auto-shrink settings, this job helps maintain optimal database performance and ensure compliance with operational best practices in SQL Server environments.