Monitors for the free space left in the transaction log in percentage terms.
This monitor checks the transaction log absolute free space in Megabytes to ensure it is above the defined thresholds. A warning or error alert will be raised if it is below one of the defined thresholds.
Transaction logs that have autogrow set will never generate an alert regardless of the reported free space.
Out of the box, this monitor will never cross a threshold as both thresholds are set to -1. Monitoring using percentage free space is the default behavior. If it is required to monitor using absolute free space, the threshold values for this monitor should be overridden
An unhealthy state is caused by the transaction log running low on free space. This could be due to rapid growth or may have been developing slowly over time. Use the following link to view the historic data for this database:
Database Performance Data
This issue may be resolved by either:
Allocating more storage for the transaction log
Setting at least one file to autogrow for the transaction log
Alternatively if transaction log size is not a concern for this database:
Change the thresholds on the monitor for this specific database or all databases
Disable the monitor for this specific database or all databases
Target | Microsoft.SQLServer.2008.Database | ||
Parent Monitor | System.Health.PerformanceState | ||
Category | PerformanceHealth | ||
Enabled | False | ||
Alert Generate | True | ||
Alert Severity | MatchMonitorHealth | ||
Alert Priority | Normal | ||
Alert Auto Resolve | True | ||
Monitor Type | Microsoft.SQLServer.2008.DBAndLogSizeMonitorType | ||
Remotable | True | ||
Accessibility | Public | ||
Alert Message |
| ||
RunAs | Default |
<UnitMonitor ID="Microsoft.SQLServer.2008.Database.TransactionLogSizeMegabytesMonitor" Accessibility="Public" Enabled="false" Target="SQL2008Core!Microsoft.SQLServer.2008.Database" ParentMonitorID="SystemHealth!System.Health.PerformanceState" Remotable="true" Priority="Normal" TypeID="Microsoft.SQLServer.2008.DBAndLogSizeMonitorType" ConfirmDelivery="false">
<Category>PerformanceHealth</Category>
<AlertSettings AlertMessage="Microsoft.SQLServer.2008.Database.TransactionLogSizeMegabytesMonitor.AlertMessage">
<AlertOnState>Warning</AlertOnState>
<AutoResolve>true</AutoResolve>
<AlertPriority>Normal</AlertPriority>
<AlertSeverity>MatchMonitorHealth</AlertSeverity>
<AlertParameters>
<AlertParameter1>$Target/Host/Host/Property[Type="Windows!Microsoft.Windows.Computer"]/NetworkName$</AlertParameter1>
<AlertParameter2>$Target/Host/Property[Type="SQL!Microsoft.SQLServer.ServerRole"]/InstanceName$</AlertParameter2>
<AlertParameter3>$Target/Property[Type="SQL!Microsoft.SQLServer.Database"]/DatabaseName$</AlertParameter3>
</AlertParameters>
</AlertSettings>
<OperationalStates>
<OperationalState ID="UnderThreshold1" MonitorTypeStateID="UnderThreshold1" HealthState="Error"/>
<OperationalState ID="OverThreshold1UnderThreshold2" MonitorTypeStateID="OverThreshold1UnderThreshold2" HealthState="Warning"/>
<OperationalState ID="OverThreshold2" MonitorTypeStateID="OverThreshold2" HealthState="Success"/>
</OperationalStates>
<Configuration>
<IntervalSeconds>900</IntervalSeconds>
<SyncTime/>
<ConnectionString>$Target/Host/Property[Type="SQL!Microsoft.SQLServer.DBEngine"]/ConnectionString$</ConnectionString>
<ObjectName>$Target/Host/Property[Type="SQL!Microsoft.SQLServer.DBEngine"]/PerformanceCounterObject$:Database</ObjectName>
<CounterName>Log Free Space (MB)</CounterName>
<InstanceName>$Data/Property[@Name='Database']$</InstanceName>
<DatabaseName>$Target/Property[Type="SQL!Microsoft.SQLServer.Database"]/DatabaseName$</DatabaseName>
<Value>$Data/Property[@Name='TransactionLogFree']$</Value>
<Threshold1>-1</Threshold1>
<Threshold2>-1</Threshold2>
<AutoGrowSet>$Target/Property[Type="SQL!Microsoft.SQLServer.Database"]/LogAutogrow$</AutoGrowSet>
<TimeoutSeconds>300</TimeoutSeconds>
</Configuration>
</UnitMonitor>