False alarms from "DBSS - File Group Utilization" rule when the database files are in a remote storage (4375823)

The rule DBSS - File Group Utilization generates false alarms for a few File Groups that contain database files in a remote storage like DFS share, NetApp Data Storage or a mix.

The SQL Server agent collects the details about these files but excludes some of the remote locations from the collection. This issue was logged as FOG-8016.

Even with the fix for FOG-8016, if the SQL Server Instance and Windows OS are not "aware" of the available space in the remote storage location, the available space may be incorrect.

WORKAROUND

Without having visibility to the available storage space for the database files to "autogrow", the rule will not be able to determine when it should fire. A possible remediation is to filter out any File Groups that contain files where the path is in a remote location by the presence of an UNC path, i.e. beginning with backslash ("\") like "\\dfs.example.com\sqlfiles".

This code snippet can be added to a copy of the DBSS - File Group Utilization rule to filter out any File Groups with files that being with backslash:

// snippet to exclude any remote files
try {
	files = scope.files;
	for (file in files){
		if (file.path.startsWith("\\")){
			return false;
		}
	}
} catch(Exception ex){
	print("DBSS - File Group Utilization (excluding remote) error: " + ex);
}

1. Navigate to the Rules dashboard (Administration | Rules & Notifications | Rules)
2. Locate DBSS - File Group Utilization and Click on the name of the rule
3. Click Copy from the context menu
4. Add a name for the new rule like DBSS - File Group Utilization (exclude remote) and click OK
5. In the Rule Editor, make sure to select Condition & Actions
6. Expand the Fatal severity accordion
7. Edit the condition to insert the previous code snippet between the first statement and the second statement as shown in the screenshot
8. (Optional) Click Test Rule Logic to validate the syntax
9. Repeat the steps 6 through 8 for Critical and Warning
10. Click Save

STATUS

Fixed in the 7.2.2.10 release of the SQL Server cartridge. Even with a fix for FOG-8016, it may still be necessary to apply the workaround above.