Lesson Learned #420: Monitoring Azure SQL Transaction Log Usage with PowerShell

Database administrators and IT professionals often need to be proactive in monitoring resources, especially when working in cloud environments like Azure SQL. One critical resource that requires monitoring is the transaction log of a SQL Server database. If it fills up, it can hinder database operations, leading to potential application downtime.

In this article, we'll discuss a PowerShell script that monitors transaction log usage in Azure SQL databases and sends an email alert if a database exceeds a specific threshold.

If you need more information about the queries that are using the transaction log see this article: Lesson Learned #7: Monitoring the transaction log space of my database - Microsoft Community Hub

Why Monitor Transaction Log Usage?

The transaction log is a vital component of any SQL Server database, recording all modifications to the database. If the log becomes full or runs out of space, it can prevent users from adding data to the database, and operations can fail.

Script Overview

Our PowerShell script does the following:

1. Connects to the Azure SQL server and retrieves a list of all databases.
   
2. For each database, it calculates the transaction log usage percentage.
   
3. If the log usage exceeds a predefined threshold, the script sends an email alert to the specified email address.
   

We also incorporated a logging mechanism to track the script's progress and any issues. The script uses a simple retry mechanism for database operations to ensure resilience against intermittent connectivity issues.

Key Components of the Script

• 
  Connection and Email Details: Before executing the script, ensure that you update the placeholders for the Azure SQL Server name, username, password, SMTP server details, and email addresses.
   
   
  
• 
  Logging Mechanism: The Write-Log function writes timestamped messages to a specified log file. It helps track the script's operations and troubleshoot any issues.
   
   
  
• 
  Retry Mechanism: Database operations can sometimes fail due to transient network issues. We've incorporated a retry policy for our database connections and queries, ensuring the script retries an operation a few times before giving up.
   
   
  
• 
  SMTP Email Alert: We used the Net.Mail.SmtpClient .NET class to send email alerts. It allows for more advanced SMTP configurations, including custom ports and authentication.
   
  

Executing the Script

To run the script:

1. Update the placeholder values in the script with your specific details.
   
2. Ensure the machine or environment running the script has internet access to connect to the Azure SQL Database and SMTP server.
   
3. Execute the script using PowerShell.
   

The script will then log its progress, check each database's log usage, and send email alerts if needed.

Security Note

Avoid keeping passwords in plaintext within scripts. Consider using Azure Key Vault or another secrets management tool to retrieve credentials securely during script execution.

Conclusion

Monitoring resources, especially critical ones like the transaction log, is essential for maintaining a healthy database environment. With our PowerShell script, administrators can gain peace of mind, knowing that they'll be alerted if any Azure SQL Database's transaction log usage exceeds acceptable levels.

Always remember to test scripts in a development or staging environment before deploying them in production to ensure they function as expected and to avoid any unintended side effects.

# Connection and Email Details

$serverName = "servername.database.windows.net"

$masterDatabase = "master"

$userId = "nnnn"

$password = "XXX"

$logFile = "C:\temp\log.txt"

$emailFrom = "NNNN@XXXX.com"

$emailTo = "N212@XXXX.com"

$subject = "Warning: High Transaction Log Usage in Azure SQL"

$smtpServer = "smtpserver.com"

$smtpUsername = "NNNN@XXXX.com"

$smtpPassword = "pwd"

$smtpPort=25

# Logging function

function Write-Log {

param(

[string]$message

)

$timestamp = Get-Date -Format "yyyy-MM-dd HH:mm:ss"

"$timestamp - $message" | Out-File $logFile -Append

}

# Retry policy settings

$maxRetries = 3

$retryDelay = 5 # in seconds

# Beginning of script execution

Write-Log "Script started."

# Get list of databases

$masterConnStr = Get-ConnectionString -server $serverName -user $userId -pass $password

Write-Log "Fetching list of databases."

$databases = Get-Databases -connectionStr $masterConnStr

# Check each database's log usage

foreach ($dbName in $databases) {

Write-Log "Checking log usage for database: $dbName."

$dbConnStr = Get-ConnectionString -server $serverName -user $userId -pass $password -database $dbName

$logUsagePercentage = Check-LogUsage -connectionStr $dbConnStr

if ($logUsagePercentage -gt 10) {

$body = "Warning: Transaction Log usage for database $dbName is $logUsagePercentage%."

Write-Log $body

$smtp = New-Object Net.Mail.SmtpClient($smtpServer, $smtpPort)

$smtp.EnableSsl = $true

$smtp.Credentials = New-Object System.Net.NetworkCredential($smtpUsername, $smtpPassword)

$mailMessage = New-Object Net.Mail.MailMessage($emailFrom, $emailTo)

$mailMessage.Subject = $subject

$mailMessage.Body = $body

$smtp.Send($mailMessage)

Write-Log "Email sent for database $dbName."

}

else {

Write-Log "Log usage for database $dbName is within acceptable limits."

}

}

# End of script execution

Write-Log "Script finished."

Continue reading...