Virtual Log File Count monitor for SQL DBs on Windows. Note: This monitor is disabled by default. Use overrides to enable it when necessary.
Virtual Log File count monitor for SQL DB
An unhealthy state is caused by having many Virtual Log Files for a database. Many Virtual Log Files can be caused by small size and 'growth_increment' values.
Use the following link to view the performance data:
Database Performance Data
This monitor counts Virtual Log Files for a database.
This issue may be resolved by either:
Backing up the transaction log, shrinking a transaction log file with the DBCC SHRINKFILE command and setting Autogrow to a larger increment
Modifying the thresholds for this monitor to suit the workload
Alternatively if Virtual Log File count is not a concern for the database:
Disable this monitor for this specific database or all databases
See SQL Server Books Online: Transaction Log Physical Architecture
Name | Description | Default Value |
Alert Priority | Defines Alert Priority. | Normal |
Alert Severity | Defines Alert Severity. | Error |
Enabled | Enables or disables the workflow. | No |
Generates Alerts | Defines whether the workflow generates an Alert. | Yes |
Interval (seconds) | The recurring interval of time in seconds in which to run the workflow. | 300 |
Number of samples | Indicates how many times a measured value should breach a threshold before the state is changed. | 6 |
Warning Threshold | The collected value will be compared against this parameter. | 100 |
Critical Threshold | The collected value will be compared against this parameter. | 1000 |
Timeout (seconds) | Specifies the time the workflow is allowed to run before being closed and marked as failed. | 200 |
Target | Microsoft.SQLServer.Windows.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.Windows.MonitorType.Database.VLFCount | ||
Remotable | True | ||
Accessibility | Public | ||
Alert Message |
| ||
RunAs | Default |
<UnitMonitor ID="Microsoft.SQLServer.Windows.Monitor.Database.VLFCount" Accessibility="Public" Enabled="false" Target="SqlDiscW!Microsoft.SQLServer.Windows.Database" ParentMonitorID="Health!System.Health.PerformanceState" Remotable="true" Priority="Normal" TypeID="Microsoft.SQLServer.Windows.MonitorType.Database.VLFCount" ConfirmDelivery="false">
<Category>PerformanceHealth</Category>
<AlertSettings AlertMessage="Microsoft.SQLServer.Windows.Monitor.Database.VLFCount.AlertMessage">
<AlertOnState>Warning</AlertOnState>
<AutoResolve>true</AutoResolve>
<AlertPriority>Normal</AlertPriority>
<AlertSeverity>MatchMonitorHealth</AlertSeverity>
<AlertParameters>
<AlertParameter1>$Target/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.Database"]/DatabaseName$</AlertParameter1>
<AlertParameter2>$Target/Host/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/InstanceName$</AlertParameter2>
<AlertParameter3>$Target/Host/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/MachineName$</AlertParameter3>
</AlertParameters>
</AlertSettings>
<OperationalStates>
<OperationalState ID="Normal" MonitorTypeStateID="Normal" HealthState="Success"/>
<OperationalState ID="Warning" MonitorTypeStateID="Warning" HealthState="Warning"/>
<OperationalState ID="Error" MonitorTypeStateID="Error" HealthState="Error"/>
</OperationalStates>
<Configuration>
<MachineName>$Target/Host/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/MachineName$</MachineName>
<NetbiosComputerName>$Target/Host/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/NetbiosComputerName$</NetbiosComputerName>
<InstanceName>$Target/Host/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/InstanceName$</InstanceName>
<DatabaseName>$Target/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.Database"]/DatabaseName$</DatabaseName>
<WarningThreshold>100</WarningThreshold>
<CriticalThreshold>1000</CriticalThreshold>
<ConnectionString>$Target/Host/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/ConnectionString$</ConnectionString>
<InstanceVersion>$Target/Host/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/Version$</InstanceVersion>
<InstanceEdition>$Target/Host/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/Edition$</InstanceEdition>
<MonitoringType>$Target/Host/Property[Type="SqlDiscW!Microsoft.SQLServer.Windows.DBEngine"]/MonitoringType$</MonitoringType>
<IntervalSeconds>300</IntervalSeconds>
<SyncTime/>
<TimeoutSeconds>200</TimeoutSeconds>
<SqlTimeoutSeconds>15</SqlTimeoutSeconds>
</Configuration>
</UnitMonitor>