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.
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.
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
To resolve virtual log files-related issues that may cause significant performance degradation, do any of the following:
Back up a transaction log or shrink it with the `DBCC SHRINKFILE` command and set the `Autogrowth` setting to a larger increment.
Modify the associated thresholds of this monitor to suit the workload.
If the virtual log file count is not a concern for the database, disable this monitor for the specific database or for all databases.
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 |
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 |
Target | Microsoft.SQLServer.Linux.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.Linux.MonitorType.Database.VLFCount | ||
Remotable | True | ||
Accessibility | Public | ||
Alert Message |
| ||
RunAs | Default |
<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>