Non-Critical State

Microsoft.SQLServerAppliance.PDW.StorageInternal.VolumeStateMonitor.NonCritical (UnitMonitor)

This monitor detects if volume free space is in Non-Critical state.

Knowledge Base article:

Summary

The used disk space for the current volume is approaching the critically low threshold.

Causes

The used disk space for the current volume is between 70% and 90% full. This has many possible causes, including:

Databases growing too quickly. The databases could have too many tables, the tables could be growing faster than expected, or both.
Too many databases created and not removed.

External facing nodes are storing too many user files. For example, the Landing Zone node might have too many large load files.

Resolutions

To diagnose the issue:

1) Use the PDW Admin Console or health DMVs to view the current values for the properties of the volume.

2) Review the disk space used on this volume. To calculate disk space used, subtract the value for volume_free_space from volume_size.

3) Use the volume_name to help determine if TEMPDB is an issue or to determine the distribution of user data stored on this volume.

4) Use DBCC SHOWSPACEUSED to determine which databases and tables are consuming the most disk space.

To resolve this issue:

1) Delete unnecessary databases or user files to reduce the data stored on the volume.

2) If log space is large then try using DBCC SHRINKLOG to reduce reserved space.

3) If the previous suggestions do not resolve the issue, contact Microsoft Support.

Element properties:

Target

Microsoft.SQLServerAppliance.PDW.StorageInternal.Volume

Parent Monitor

System.Health.AvailabilityState

Category

AvailabilityHealth

Enabled

True

Alert Generate

True

Alert Severity

Warning

Alert Priority

Normal

Alert Auto Resolve

True

Monitor Type

Microsoft.SQLServerAppliance.PDW.ComponentTwoStateType

Remotable

True

Accessibility

Public

Alert Message

Volume free space status is NON-CRITICAL

Appliance Name: {0}
Node Name: {1}
Component: {2}
Component Details: https://{3}/Topology/NodeDetails/{4}?compId={5}

RunAs

Default

Source Code:

<UnitMonitor ID="Microsoft.SQLServerAppliance.PDW.StorageInternal.VolumeStateMonitor.NonCritical" Accessibility="Public" Enabled="true" Target="PDWLibrary!Microsoft.SQLServerAppliance.PDW.StorageInternal.Volume" ParentMonitorID="Health!System.Health.AvailabilityState" TypeID="Microsoft.SQLServerAppliance.PDW.ComponentTwoStateType" Remotable="true" Priority="Normal" ConfirmDelivery="false">

  <Category>AvailabilityHealth</Category>

  <AlertSettings AlertMessage="Microsoft.SQLServerAppliance.PDW.StorageInternal.VolumeStateMonitor.NonCritical.AlertMessage">

    <AlertOnState>Warning</AlertOnState>

    <AutoResolve>true</AutoResolve>

    <AlertPriority>Normal</AlertPriority>

    <AlertSeverity>Warning</AlertSeverity>

    <AlertParameters>

      <AlertParameter1>$Target/Property[Type="PDWLibrary!Microsoft.SQLServerAppliance.PDW.Component"]/ApplianceID$</AlertParameter1>

      <AlertParameter2>$Target/Property[Type="PDWLibrary!Microsoft.SQLServerAppliance.PDW.Component"]/NodeName$</AlertParameter2>

      <AlertParameter3>$Target/Property[Type="System!System.Entity"]/DisplayName$</AlertParameter3>

      <AlertParameter4>$Target/Property[Type="PDWLibrary!Microsoft.SQLServerAppliance.PDW.Component"]/ApplianceNetworkAddress$</AlertParameter4>

      <AlertParameter5>$Target/Property[Type="PDWLibrary!Microsoft.SQLServerAppliance.PDW.Component"]/NodeID$</AlertParameter5>

      <AlertParameter6>$Target/Property[Type="PDWLibrary!Microsoft.SQLServerAppliance.PDW.Component"]/ID$</AlertParameter6>

    </AlertParameters>

  </AlertSettings>

  <OperationalStates>

    <OperationalState ID="Good" MonitorTypeStateID="Good" HealthState="Success"/>

    <OperationalState ID="Bad" MonitorTypeStateID="Bad" HealthState="Warning"/>

  </OperationalStates>

  <Configuration>

    <IntervalSeconds>900</IntervalSeconds>

    <SyncTime/>

    <TimeoutSeconds>600</TimeoutSeconds>

    <ConnectionString>$Target/Property[Type="PDWLibrary!Microsoft.SQLServerAppliance.PDW.Component"]/ApplianceID$</ConnectionString>

    <NodeName>$Target/Property[Type="PDWLibrary!Microsoft.SQLServerAppliance.PDW.Component"]/NodeName$</NodeName>

    <GroupName>$Target/Property[Type="PDWLibrary!Microsoft.SQLServerAppliance.PDW.Component"]/GroupName$</GroupName>

    <ComponentName>$Target/Property[Type="System!System.Entity"]/DisplayName$</ComponentName>

    <MonitoredState>NonCritical</MonitoredState>

  </Configuration>

</UnitMonitor>