Transaction log space usage (2008-2016)

Name:	Transaction log space usage
Platform:	Sqlserver
Category:	Capacity
Description:	This procedure checks information returned by DBCC SQLPERF which is used to monitor the amount of space used in the transaction log.
Long description:	The procedure indicates (by warning or alarm) when to back up or truncate the transaction logs. If parameter ‘include unallocated free space’ is set to ‘YES’, the procedure will compute the free space in each transaction log file based on available space in each file plus all unallocated space this file (or files) could autoextend to (maxsize – current size).
Version:	2.1
Default schedule:	10,40 * * *
Requires engine install:	Yes
Compatibility tag:	[CDATA[.[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version > ‘2005’ & maj_version < ‘2017’ & hasengine=‘YES’ & (engine_edition = ‘Microsoft SQL Server’ \| engine_edition = ‘Azure SQL Managed Instance’)

Parameters
Name	Default value	Description
min histr interval	55	The minimum number of minutes before a new history record will be inserted in the history table.
warning threshold	80	The maximum percentage of space usage allowed in the transaction log before a warning is triggered.
alarm threshold	95	The maximum percentage of space usage allowed in the transaction log before an alarm is triggered.
history threshold	96	The number of hours the statistics are kept in the history table.
min transaction log size	1000	The minimum size (in MB) of the transaction log files to be examined.
max num db	15	The maximum number of databases to be collected in the history tables (sort by transaction log size).
include unallocated free space	YES	If set to “YES”, the procedure will compute the free space in each transaction log file based on available space in each file plus all unallocated space this file (or files) could autoextend to (maxsize – current size).

Feedback