Virtual Log File Count

Microsoft.SQLServer.Linux.Monitor.Database.VLFCount (UnitMonitor)

Virtual Log File Count monitor for SQL DBs on Linux. Note that this monitor is disabled by default. Use overrides to enable it when necessary.

Knowledge Base article:

Summary

This monitor counts the number of virtual log files (VLFs) created automatically by the SQL Server Database Engine that internally divides each physical log file into a number of virtual log files. Having too many virtual log files may slow down database startup and also log backup and restore operations. If the log files are set to a large size with few or just one increment, they will have only few very large virtual log files. The size or number of virtual log files cannot be configured or set explicitly.

Causes

An unhealthy state produced by this monitor is caused by having an excessive amount of virtual log files. Such an excessive amount is the result of log files that grow to a large size in many small increments. To avoid an excessive amount of virtual log files, make sure to properly estimate both the `required size` setting and the `Autogrowth` setting of a transaction log.

Use the following link to view the performance data: Database Performance Data

Resolutions

To resolve virtual log files-related issues that may cause significant performance degradation, do any of the following:

External

Transaction Log Physical Architecture

Overridable Parameters

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

Timeout for query execution (seconds)

The workflow will fail and register an event, if the query execution takes longer than the specified period.

60

Timeout for database connection (seconds)

The workflow will fail and register an event, if it cannot access the database during the specified period.

15

Element properties:

TargetMicrosoft.SQLServer.Linux.Database
Parent MonitorSystem.Health.PerformanceState
CategoryPerformanceHealth
EnabledFalse
Alert GenerateTrue
Alert SeverityMatchMonitorHealth
Alert PriorityNormal
Alert Auto ResolveTrue
Monitor TypeMicrosoft.SQLServer.Linux.MonitorType.Database.VLFCount
RemotableTrue
AccessibilityPublic
Alert Message
MSSQL on Linux: Many Virtual Log Files
There are many virtual log files for database "{2}" on SQL Server instance "{1}", computer "{0}". See the "Alert Context" tab for more details.
RunAsDefault

Source Code:

<UnitMonitor ID="Microsoft.SQLServer.Linux.Monitor.Database.VLFCount" Accessibility="Public" Enabled="false" Target="SqlDiscL!Microsoft.SQLServer.Linux.Database" ParentMonitorID="Health!System.Health.PerformanceState" Remotable="true" Priority="Normal" TypeID="Microsoft.SQLServer.Linux.MonitorType.Database.VLFCount" ConfirmDelivery="false">
<Category>PerformanceHealth</Category>
<AlertSettings AlertMessage="Microsoft.SQLServer.Linux.Monitor.Database.VLFCount.AlertMessage">
<AlertOnState>Warning</AlertOnState>
<AutoResolve>true</AutoResolve>
<AlertPriority>Normal</AlertPriority>
<AlertSeverity>MatchMonitorHealth</AlertSeverity>
<AlertParameters>
<AlertParameter1>$Target/Host/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/MachineName$</AlertParameter1>
<AlertParameter2>$Target/Host/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/InstanceName$</AlertParameter2>
<AlertParameter3>$Target/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.Database"]/DatabaseName$</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>
<IntervalSeconds>300</IntervalSeconds>
<SyncTime/>
<TimeoutSeconds>200</TimeoutSeconds>
<SqlExecTimeoutSeconds>60</SqlExecTimeoutSeconds>
<SqlTimeoutSeconds>15</SqlTimeoutSeconds>
</Configuration>
</UnitMonitor>