Case Study
Azure SQL Maintenance Automation
A scheduled Azure Automation runbook that keeps SQL elastic pools healthy — shrinks wasted log space, resizes databases to demand, adjusts pool capacity, and emails a full audit trail after every run.
The Challenge
Storage Drift Costs Money and Performance
Azure SQL elastic pools grow unevenly. Some databases balloon with unused log space; others need more room overnight. Manual resizing is slow, inconsistent, and easy to postpone.
Over-allocated databases waste cloud spend. Under-provisioned pools risk throttling or failed writes when workloads spike. Firewall access for maintenance workers is another manual step — open a rule, run the job, remember to close it. Without an audit trail, nobody can prove what changed overnight.
We needed a repeatable maintenance job that could run on Azure workers or hybrid workers, stay secure when VNet integration was unavailable, and leave operators with a clear report instead of a silent portal change.
The Solution
One Runbook, Full Pool Hygiene
We built a PowerShell 7.2 Azure Automation script that assesses the elastic pool, remediates each database, manages temporary firewall access, and emails a structured HTML report.
What the job does
- Checks elastic pool free space and resizes the pool when capacity drifts outside target bands
- Shrinks database log files when unused space exceeds 20% of allocated storage
- Grows databases that need headroom — targeting ~50% more space than current use
- Adds a temporary firewall rule for the worker IP, then removes it when finished
- Emails a summary of shrinks, resizes, pool changes, and errors
Why it matters
- Performance — lean storage and right-sized files keep queries from fighting bloated allocations
- Cost — pay for capacity you need, not leftover unused space
- Scale — pools grow with the workload without overnight pager duty
- Security — temporary firewall rules leave no permanent worker IP exposure
In Production
Every Run Leaves an Audit Trail
Operators get an HTML email after each job — totals, per-database changes, and a step-by-step log of authentication, checks, and SQL operations.
Technology
What We Used
Designed for Azure Automation Accounts with either Azure workers or hybrid workers — no custom portal dependency.
Platform & tooling
Engineering approach
The runbook authenticates to Azure, optionally opens a temporary SQL firewall rule for the worker, adjusts the elastic pool first so individual database resizes have room to succeed, then walks each database with direct SQL for space accounting and shrink operations. All actions are recorded in structured collections that render into the email report — so a failed shrink or resize is visible in the same place as the successes.
The Result
Hands-Off Pool Hygiene
Routine storage maintenance no longer depends on someone remembering to check the portal. The pool stays within target free-space bands, databases reclaim wasted log space, and every change is emailed.
Similar Challenge?
Automate Your Azure SQL Ops
If elastic pools are growing by hand, storage costs are climbing without explanation, or overnight maintenance still depends on a laptop and a calendar reminder — we build Azure Automation that keeps databases lean and leaves a paper trail.