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:
- Connects to the Azure SQL server and retrieves a list of all databases.
- For each database, it calculates the transaction log usage percentage.
- 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:
- Update the placeholder values in the script with your specific details.
- Ensure the machine or environment running the script has internet access to connect to the Azure SQL Database and SMTP server.
- 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
}
# Generate connection string
function Get-ConnectionString {
param (
[string]$server,
[string]$user,
[string]$pass,
[string]$database = "master"
)
return "Server=$server;Database=$database;User Id=$user;Password=$pass;"
}
# Function to execute SQL query with retry logic
function Execute-SqlQuery {
param(
[string]$connectionStr,
[string]$sqlQuery
)
$retryCount = 0
while ($retryCount -lt $maxRetries) {
try {
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionStr
$command = $connection.CreateCommand()
$command.CommandText = $sqlQuery
$connection.Open()
$reader = $command.ExecuteReader()
$results = @()
while ($reader.Read()) {
$row = @{}
for ($i = 0; $i -lt $reader.FieldCount; $i++) {
$row[$reader.GetName($i)] = $reader.GetValue($i)
}
$results += $row
}
$connection.Close()
return $results
} catch {
$retryCount++
Write-Log "An error occurred while executing SQL query. Attempt $retryCount of $maxRetries."
Start-Sleep -Seconds $retryDelay
}
}
throw "Failed to execute SQL query after $maxRetries attempts."
}
# Get list of databases from SQL Server
function Get-Databases {
param (
[string]$connectionStr
)
$sqlQuery = "SELECT name FROM sys.databases WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb')"
$results = Execute-SqlQuery -connectionStr $connectionStr -sqlQuery $sqlQuery
return $results | ForEach-Object { $_["name"] }
}
# Check log usage for a specific database
function Check-LogUsage {
param (
[string]$connectionStr
)
$sqlQuery = "SELECT (used_log_space_in_bytes * 1.0 / total_log_size_in_bytes) * 100 AS LogUsagePercentage FROM sys.dm_db_log_space_usage"
$results = Execute-SqlQuery -connectionStr $connectionStr -sqlQuery $sqlQuery
return [math]::Round([double]$results["LogUsagePercentage"], 2)
}
# 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."
