Monitor SQL Database Free space

This example will show a way to customize monitoring of SQL servers for database free space on VM’s in Azure.

Note this is example will be with VM’s hosted on Azure. If you want to simulate with on prem servers see hybrid runbook worker overview: Azure Automation Hybrid Runbook Worker overview | Microsoft Docs

First create automation account if don’t already have one in your Azure subscription: Azure Quickstart – Create an Azure Automation account | Microsoft Docs

Next create PowerShell runbook: Create a PowerShell Workflow runbook in Azure Automation | Microsoft Docs

Use this script in your runbook. Schedule the run book to run on some interval you find sufficient for this monitoring. In my example I scheduled this script to run every 15 minutes.

param(
[string]$serverInstance = ‘localhost’
)

$evtlog = “Application”
$source = “MySQLCustom”

$hostname = [Environment]::MachineName
$timestamp = (get-date)

if ([System.Diagnostics.EventLog]::SourceExists($source) -eq $false) {
[System.Diagnostics.EventLog]::CreateEventSource($source, $evtlog)
}

function CreateParamEvent ($evtID, $param1, $param2, $param3, $param4, $param5)
{
$id = New-Object System.Diagnostics.EventInstance($evtID,1); #INFORMATION EVENT
#$id = New-Object System.Diagnostics.EventInstance($evtID,1,2); #WARNING EVENT
#$id = New-Object System.Diagnostics.EventInstance($evtID,1,1); #ERROR EVENT
$evtObject = New-Object System.Diagnostics.EventLog;
$evtObject.Log = $evtlog;
$evtObject.Source = $source;
$evtObject.WriteEvent($id, @($param1,$param2,$param3,$param4,$param5))
}

CreateParamEvent 1230 “Begin script” $hostname $timestamp ” ” ” “

$outputs = @();

Get-SqlDatabase -serverinstance $serverInstance | %{
$db = $_
$db.FileGroups[0].Files| %{
$output = New-Object -TypeName PSObject -Property @{
DatabaseName = $db.Name
FileLogicalName = $_.Name
FileLocation = $_.FileName
FileGrowthKB = $_.Growth
MaxSizeKB = $_.MaxSize
PercentFreeSpace = [Math]::Truncate(($_.AvailableSpace / $_.Size) * 100)
}
$outputs += $output
}
}
$outputs | SELECT DatabaseName, FileLogicalName, FileLocation, PercentFreeSpace, FileGrowthKB, MaxSizeKB

foreach ($db in $outputs) {
$dbName = $db.DatabaseName.ToString()
$dbPercentFreeSpace = Int
if ($dbPercentFreeSpace -lt 0) { $dbPercentFreeSpace = $dbPercentFreeSpace * -1; }

if ($dbPercentFreeSpace -le 10) {
CreateParamEvent 1234 “The database $dbName on server $hostname is running low on free space. Current percent free is $dbPercentFreeSpace .” $hostname $timestamp $dbName $dbPercentFreeSpace
}

}

CreateParamEvent 1239 “End Script” $hostname $timestamp ” ” ” “

Ensure that your agents are collecting Application event logs by viewing advanced settings on the workspace.

In your Default workspace select to run the following event query.

Event | where Source contains “MySQLCustom” and EventID == 1234

Next select the + New alert rule to the right of the Run button. This will provide us the ability to create an alert when our PowerShell script returns the event indicating one of our databases has crossed our threshold for free space.

Enter a Name for the alert rule. and configure the condition information.

Select the action group to email whom you would like to contact when the threshold is exceeded. Create new action group if needed for a new users email for notifications.

Provide the Severity settings for your new alert and select to Suppress alerts if you would like to prevent email notifications every time the script runs.

Select to create and save the new alert rule.

Below is an example of the format of the alerting rule email that was generated from this example. As you can see the message of the email will display the results from our PowerShell script Event log entry that we wrote out when our threshold was crossed.

This was one example of how you can use Azure runbook to run PS Script on SQL servers and allow for notifications using alert rules in Azure.

As one might imagine, this workflow could be modified or duplicated to perform any type of check you can perform with a PowerShell script. Leveraging writing events to the Windows Event log for Azure Monitor to collect and then allow for alerting on a multitude of custom scenarios.