Shrink transaction logs

Job details

Name:	Shrink transaction logs
Platform:	Sqlserver
Category:	Maintenance
Premium package:	Maintenance package
Description:	This procedure shrinks transaction log files which are detected by ‘Transaction log size check’ alert which checks the size of the transaction log (log file(s) size), and compares it to the database size (data file(s) size).
Long description:	This procedure shrinks transaction log files which are detected by ‘Transaction log size check’ alert which checks the size of the transaction log (log file(s) size), and compares it to the database size (data file(s) size). Only transaction logs with one file can be shrinked.
Version:	1.2
Default schedule:	10 5 6 *
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
min transaction log size	2000	The minimum size (in MB) of the transaction log files that will be shrinked.
max transaction log size	10000	The maximum size (in MB) of the transaction log files that will be shrinked.
ratio threshold (%)	300	The ratio threshold (in percent) between the data file size and the transaction log file size. If the size of the transaction log file is twice as large in comparison with the total size of the data file(s), then the ratio is 200%. Only transaction log files above this threshold will be shrinked.
ignore databases		The list of databases to ignore (separated by comma).
enable shrinking	NO	Set to YES to enable shrinking.
ratio threshold after (%)	80	The ratio threshold (in percent) between the data file size and the transaction log file size after shrinking.
history threshold	10	Shrink history records per database.

Job Summary

Purpose: This job shrinks transaction log files that exceed the size ratio compared to the database size and have only one log file. It’s designed to prevent inefficient and excessive disk space usage by transaction logs.

Why: Keeping transaction log sizes under control is crucial for efficient disk space utilization and database performance. When the transaction log grows too large compared to the database size, it can lead to wasted space and potential performance issues. This job is also essential to maintain database health in SQL Server instances (including Azure SQL Managed Instances) from version 2006 onwards.

Manual checking: You can check these manually in the database by using the following SQL command, assuming proper SQL permissions:

SELECT dbname "Database", round(cast(data_size as bigint), 1) "Data size", num_data_files "Data file count", round(cast(log_size as bigint), 1) "Log size", num_log_files "Log file count", round(ratio, 1) "Ratio", log_space_used_pct "Log space used", histr_date "Warning date"
FROM dbw_shrink_trans_logs_table
ORDER BY ratio DESC;

Operations and Execution

This job operates in two main contexts:
- Monitoring: Establishes the current state of transaction logs, checking against predetermined thresholds to identify candidates for shrinking.

Targets and Thresholds

Eligibility for Operation: Target SQL Server and Azure SQL Managed Instance databases where:
- Database main version is newer than 2005.

Thresholds for Execution:
- Only logs where their size is considerably disproportionate (specified ratio threshold) to the database size are targeted.

Parameters Handled

(This section is not described in detail as per request, but includes thresholds for minimum and max transaction log sizes, ratios, databases to ignore, and enabling or disabling shrinking as major parameters affecting job execution.)

Dependencies

This job has inherent dependencies on:
- A couple of helper tables for storing transaction logs past data.

Automation and Reporting

Implements full automation capabilities, only executing (shrinking logs) under specific conditions and maintaining a historical log for audits and reviews.

Transaction log reports and history are synthesized and can be utilized for in-depth analysis or provided during audits.

Version and Maintenance

Version: 1.2
Maintained by dbwatch.no
Has undergone upgrades from previous versions to refine parameter management and procedural logic.

Note for installations:
- Non-express installation flagged.

Extending and Customization

Potential for further customization via manual override in parameters, scheduled tasks adaptability to meet specific organizational needs, or adjusting thresholds according to database growth and usage patterns.