Truncate transaction log

Job details

Parameters

Job Summary

• Purpose: This monitoring job is designed to truncate the transaction log in the dbWatch database on SQL Server, specifically tailored for SQL Server 2000 with engine edition ‘Microsoft SQL Server’.
• Why: Maintaining an optimal size of the transaction log is critical to prevent it from consuming excessive disk space, which can lead to performance degradation and system unavailability. If the transaction log grows too large, it could fill up the disk space, causing database operations to halt.
• Manual checking: In order to manually check the size of transaction logs in SQL Server, administrators can use the following SQL command:

SELECT name, filename, size * 8 / 1024 AS size FROM sysfiles;

Job Details

Description

The task named “Truncate transaction log” aims to manage the size of the transaction log by truncating it. Truncation is done when the log size exceeds a certain threshold, which helps in keeping the database healthy and preventing log files from using up too much disk space.

Implementation Logic

The procedure creates and uses several variables to ascertain the log’s size and perform truncation if necessary. Here is the summarized procedure:

• Variables are initialized to fetch the database and transaction log details.
• If the transaction log size is 15MB or more, it gets truncated.
• The SQL Server’s BACKUP LOG command with TRUNCATE_ONLY option and DBCC SHRINKFILE are used to truncate the log.
• Task details are updated with the new size after truncation.

Dependencies

The procedure depends on several system databases and tables:

• “master.dbo.sysaltfiles” – provides details about the data and log files.
• “master.dbo.sysdatabases” – provides database-level information.
• “master.dbo.sysfiles” – gives detailed information about all files associated with the database.

All dependent objects are crucial for fetching the required information to perform and validate the truncation process.

Reporting & Monitoring

Report Template

A report template named “dbWatch transaction Log size” exists as part of this job to display the before and after states of the transaction log:

• The report displays information from the SYSFILES system table.
• A formatted table in the report shows file names, locations, and sizes which helps in auditing and historical tracking.

Default Schedule

• The job “Truncate transaction log” is scheduled to run every 10 minutes.
• The report gathers and displays data at a scheduled rate of once per hour.

Summary tables and charts are made available within the dbWatch Control Center interface allowing users to monitor and review the size changes in the transaction logs over time actively.