DB FILESTREAM Filegroup Free Space

Microsoft.SQLServer.2016.Monitoring.DBFileStreamGroupSpaceMonitor (UnitMonitor)

The monitor reports a warning when the free space drops below the Warning Threshold setting, expressed as percentage of the sum of data size. The monitor reports a critical alert when the free space drops below the Critical Threshold.

Knowledge Base article:

Summary

The monitor reports a warning when the available disk space for the FILESTREAM Filegroup Container drops below the Warning Threshold value expressed as percentage of the disk free space total. The monitor reports a critical alert when the free space drops below the Critical Threshold.

The FILESTREAM Filegroup data container folder typically grows over time while more data is added into the tables. Therefore, administrators will wish to monitor this parameter to ensure there is sufficient space remaining in the storage location for the container folder.

Causes

When the available disk space on the drive where the FILESTREAM data container is configured becomes too low, some failures may occur preventing successful processing of data, delaying user’s queries, or affecting other applications sharing the same allocation of the disk space.

Resolutions

The resolution(s) for low available disk space depend upon the root causes, which must be investigated in each case when this issue occurs. The condition may be alleviated by any of the following potential approaches:

Increase the free space on the logical disk storing FILESTREAM Filegroup data container.
Increase the maximal limit for FILESTREAM Filegroup data container.
Add a new data container on the other logical disk.

Overrideable Parameters

Name	Description	Default Value
Alert Priority	Defines Alert Priority.	Normal
Alert Severity	Defines Alert Severity.	Error
Azure Maximum File Size (MB)	Azure Maximum File Size (MB)	1048576
Critical Threshold	The monitor will change its state to Critical if the value drops below this threshold. Being between this threshold and the warning threshold (inclusive) will result in the monitor being in a warning state.	10
Enabled	Enables or disables the workflow.	Yes
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.	900
Synchronization Time	The synchronization time specified by using a 24-hour format. May be omitted.
Timeout (seconds)	Specifies the time the workflow is allowed to run before being closed and marked as failed.	300
Warning Threshold	The monitor will change its state to Warning if the value drops below this threshold.	20

Element properties:

Target

Microsoft.SQLServer.2016.DBFileGroupFd

Parent Monitor

System.Health.PerformanceState

Category

PerformanceHealth

Enabled

True

Alert Generate

True

Alert Severity

Error

Alert Priority

Normal

Alert Auto Resolve

True

Monitor Type

Microsoft.SQLServer.2016.DBFileStreamGroupSizeMonitorType

Remotable

True

Accessibility

Public

Alert Message

MSSQL 2016: DB FILESTREAM Filegroup data container is running out of space

DB FILESTREAM Filegroup data container for Filegroup "{0}" in the database "{1}" in SQL instance "{2}" on computer "{3}" is running out of space. See knowledge section for more details.

RunAs

Default

Source Code:

<UnitMonitor ID="Microsoft.SQLServer.2016.Monitoring.DBFileStreamGroupSpaceMonitor" Accessibility="Public" Enabled="true" Target="SQL2016Core!Microsoft.SQLServer.2016.DBFileGroupFd" ParentMonitorID="SystemHealth!System.Health.PerformanceState" Remotable="true" Priority="Normal" TypeID="Microsoft.SQLServer.2016.DBFileStreamGroupSizeMonitorType" ConfirmDelivery="true">

  <Category>PerformanceHealth</Category>

  <AlertSettings AlertMessage="Microsoft.SQLServer.2016.Monitoring.DBFileStreamFileGroupSpaceMonitor.AlertMessage">

    <AlertOnState>Error</AlertOnState>

    <AutoResolve>true</AutoResolve>

    <AlertPriority>Normal</AlertPriority>

    <AlertSeverity>Error</AlertSeverity>

    <AlertParameters>

      <AlertParameter1>$Target/Property[Type="SQL2016Core!Microsoft.SQLServer.2016.FileGroup"]/GroupName$</AlertParameter1>

      <AlertParameter2>$Target/Host/Property[Type="SQL2016Core!Microsoft.SQLServer.2016.Database"]/DatabaseName$</AlertParameter2>

      <AlertParameter3>$Target/Host/Host/Property[Type="SQL2016Core!Microsoft.SQLServer.2016.ServerRole"]/InstanceName$</AlertParameter3>

      <AlertParameter4>$Target/Host/Host/Host/Property[Type="Windows!Microsoft.Windows.Computer"]/NetworkName$</AlertParameter4>

    </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/Host/Property[Type="SQL2016Core!Microsoft.SQLServer.2016.DBEngine"]/ConnectionString$</ConnectionString>

    <ServerName>$Target/Host/Host/Host/Property[Type="Windows!Microsoft.Windows.Computer"]/NetworkName$</ServerName>

    <SqlInstanceName>$Target/Host/Host/Property[Type="SQL2016Core!Microsoft.SQLServer.2016.ServerRole"]/InstanceName$</SqlInstanceName>

    <ObjectName>SQL DB FileStream FileGroup</ObjectName>

    <CounterName>DB FileStrem FileGroup Free Space (%)</CounterName>

    <InstanceName/>

    <DatabaseName>$Target/Host/Property[Type="SQL2016Core!Microsoft.SQLServer.2016.Database"]/DatabaseName$</DatabaseName>

    <DBFileId>$Target/Property[Type="SQL2016Core!Microsoft.SQLServer.2016.DBFileGroupFd"]/GroupID$</DBFileId>

    <Value>$Data/Property[@Name='FreeSpacePercent']$</Value>

    <Threshold1>10</Threshold1>

    <Threshold2>20</Threshold2>

    <TimeoutSeconds>300</TimeoutSeconds>

    <AzureMaxFileSizeMB>1048576</AzureMaxFileSizeMB>

  </Configuration>

</UnitMonitor>