Azure SQL Managed Instance - Log Space Growth Alert using Azure Runbook/PowerShell

[Vijay_Kumar]

Introduction

There are scenarios wherein customer want to monitor their transaction log space usage. Currently there are options available to monitor Azure SQL Managed Instance metrics like CPU, RAM, IOPS etc. using Azure Monitor, but there is no inbuilt alert to monitor the transaction log space usage.

This blog will guide to setup Azure Runbook and schedule the execution of DMVs to monitor their transaction log space usage and take appropriate actions.

Overview

Microsoft Azure SQL Managed Instance enables a subset of dynamic management views (DMVs) to diagnose performance problems, which might be caused by blocked or long-running queries, resource bottlenecks, poor query plans, and so on. 

Using DMV’s we can also find the log growth – Find the usage in percentage and compare it to a threshold value and create an alert.

In Azure SQL Managed Instance, querying a dynamic management view requires VIEW SERVER STATE permissions.

GRANT VIEW SERVER STATE TO database_user;

Monitor log space use by using sys.dm_db_log_space_usage. This DMV returns information about the amount of log space currently used and indicates when the transaction log needs truncation.

For information about the current log file size, its maximum size, and the auto grow option for the file, you can also use the size, max_size, and growth columns for that log file in sys.database_files.

Solution

Below PowerShell script can be used inside an Azure Runbook and alerts can be created to notify the user about the log space used to take necessary actions.

# Ensures you do not inherit an AzContext in your runbook
Disable-AzContextAutosave -Scope Process
$Threshold = 70  # Change this to your desired threshold percentage

try
{
    "Logging in to Azure..."
    Connect-AzAccount -Identity
}
catch {
    Write-Error -Message $_.Exception
    throw $_.Exception
}

$ServerName = "tcp:xxx.xx.xxx.database.windows.net,3342"
$databaseName = "AdventureWorks2017"
$Cred = Get-AutomationPSCredential -Name "xxxx"
$Query="USE [AdventureWorks2017];"
$Query= $Query+ " "
$Query= $Query+ "SELECT  ROUND(used_log_space_in_percent,0) as used_log_space_in_percent FROM sys.dm_db_log_space_usage;"


$Output = Invoke-SqlCmd -ServerInstance $ServerName -Database $databaseName -Username $Cred.UserName -Password $Cred.GetNetworkCredential().Password -Query $Query

#$LogspaceUsedPercentage = $Output.used_log_space_in_percent
#$LogspaceUsedPercentage
 
if($Output. used_log_space_in_percent -ge $Threshold)
 {
        # Raise an alert
        $alertMessage = "Log space usage on database $databaseName is above the threshold. Current usage: $Output.used_log_space_in_percent%."
        Write-Output "Alert: $alertMessage"
 
        # You can send an alert using Send-Alert cmdlet or any other desired method
        # Send-Alert -Message $alertMessage -Severity "High" Via EMAIL - Can call logicApp to send email, run DBCC CMDs etc.
    } else {
        Write-Output "Log space usage is within acceptable limits."
    }

There are different alert options which you can use to send alert in case log space exceeds its limit as below.

Alert Options

1. Send email using logic apps or SMTP - https://learn.microsoft.com/en-us/azure/connectors/connectors-create-api-smtp

2. Azure functions - https://learn.microsoft.com/en-us/samples/azure-samples/e2e-dotnetcore-function-sendemail/azure-net-...

3. Run dbcc command to shrink log growth - https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/file-space-manage?view=azuresql-m...

Feedback and suggestions

If you have feedback or suggestions for improving this data migration asset, please contact the Data SQL Ninja Engineering Team (datasqlninja@microsoft.com). Thanks for your support!

Note: For additional information about migrating various source databases to Azure, see the Azure Database Migration Guide