As any DBA managing SQL Server environments knows, log file growth can quickly consume available disk space. I've faced situations where unattended log file expansion nearly caused production outages. The solution lies in implementing automated alerts before reaching critical thresholds.

Here's a robust PowerShell script that checks disk space and sends email alerts when thresholds are breached:

# Disk Space Monitoring Script for Windows Server 2008
$threshold = 15 # Percentage threshold for alert
$smtpServer = "mail.yourdomain.com"
$fromAddress = "alerts@yourdomain.com"
$toAddress = "dba-team@yourdomain.com"

$disks = Get-WmiObject Win32_LogicalDisk | Where-Object { $_.DriveType -eq 3 }

foreach ($disk in $disks) {
    $freeSpace = [math]::Round(($disk.FreeSpace / $disk.Size) * 100, 2)
    
    if ($freeSpace -lt $threshold) {
        $subject = "LOW DISK SPACE ALERT: $($disk.DeviceID) on $env:COMPUTERNAME"
        $body = @"
Server: $env:COMPUTERNAME
Drive: $($disk.DeviceID)
Total Size: $([math]::Round($disk.Size/1GB,2)) GB
Free Space: $freeSpace% ($([math]::Round($disk.FreeSpace/1GB,2)) GB)
Threshold: $threshold%

Immediate action required to prevent service interruption.
"@
        Send-MailMessage -SmtpServer $smtpServer -From $fromAddress -To $toAddress -Subject $subject -Body $body
    }
}

To automate regular checks, schedule this script to run via Task Scheduler:

$action = New-ScheduledTaskAction -Execute "PowerShell.exe" -Argument "-File C:\Scripts\DiskSpaceMonitor.ps1"
$trigger = New-ScheduledTaskTrigger -Daily -At 9am
Register-ScheduledTask -TaskName "Disk Space Monitor" -Action $action -Trigger $trigger -User "SYSTEM"

For those preferring GUI configuration:

1. Open Performance Monitor (perfmon)
2. Create a Data Collector Set with performance counters for logical disks
3. Configure alerts for "% Free Space" under each disk instance
4. Set the alert action to send email via SMTP

While alerts help, proactive log management is better:

-- Set recovery model to Simple for non-critical databases
ALTER DATABASE YourDatabase SET RECOVERY SIMPLE

-- Schedule regular log backups
BACKUP LOG YourDatabase TO DISK = 'E:\Backups\YourDatabase_log.trn'

For environments with multiple SQL servers, consider:

# Centralized monitoring of multiple servers
$servers = "SQL01","SQL02","SQL03"

foreach ($server in $servers) {
    Invoke-Command -ComputerName $server -FilePath C:\Scripts\DiskSpaceMonitor.ps1
}

Database administrators know the nightmare scenario - SQL Server grinding to a halt because transaction logs consumed all available disk space. Windows Server 2008 lacks built-in email alert functionality for disk thresholds, requiring custom solutions.

While Performance Monitor can track disk metrics, it requires manual checks. For 24/7 protection, we'll implement a PowerShell script that:

1. Scans all logical drives
2. Compares free space against configurable thresholds
3. Sends SMTP alerts when critical levels are reached
4. Logs events for audit trails

Save this as DiskSpaceMonitor.ps1 and schedule via Task Scheduler:

# Parameters
$threshold = 15 # Percentage free space threshold
$smtpServer = "mail.yourdomain.com"
$toAddress = "dba@yourdomain.com"
$fromAddress = "alerts@yourdomain.com"

# Get disk information
$disks = Get-WmiObject Win32_LogicalDisk | Where-Object {$_.DriveType -eq 3}

foreach ($disk in $disks) {
    $freePercent = ($disk.FreeSpace / $disk.Size) * 100
    
    if ($freePercent -lt $threshold) {
        $subject = "CRITICAL: Low disk space on $($disk.DeviceID)"
        $body = @"
Server: $($env:COMPUTERNAME)
Drive: $($disk.DeviceID)
Total Size: $([math]::Round($disk.Size/1GB,2)) GB
Free Space: $([math]::Round($disk.FreeSpace/1GB,2)) GB ($([math]::Round($freePercent,2))%)
"@
        
        # Send email alert
        Send-MailMessage -SmtpServer $smtpServer 
                        -To $toAddress 
                        -From $fromAddress 
                        -Subject $subject 
                        -Body $body 
                        -Priority High
        
        # Log event
        Write-EventLog -LogName "Application" 
                      -Source "Disk Monitor" 
                      -EntryType Warning 
                      -EventID 1001 
                      -Message $body
    }
}

For enterprise environments, consider these enhancements:

# Per-drive thresholds
$customThresholds = @{
    "C:" = 20
    "D:" = 10
    "E:" = 15
}

# HTML formatted emails
$htmlBody = @"
<h2>Disk Space Alert</h2>
<table border=1>
<tr><th>Drive</th><th>Total</th><th>Free</th><th>% Free</th></tr>
<tr><td>$($disk.DeviceID)</td>
<td>$([math]::Round($disk.Size/1GB,2)) GB</td>
<td>$([math]::Round($disk.FreeSpace/1GB,2)) GB</td>
<td><span style='color:red'>$([math]::Round($freePercent,2))%</span></td></tr>
</table>
"@

Create a scheduled task that runs hourly:

$action = New-ScheduledTaskAction -Execute "PowerShell.exe" -Argument "-File C:\Scripts\DiskSpaceMonitor.ps1"
$trigger = New-ScheduledTaskTrigger -Once -At (Get-Date) -RepetitionInterval (New-TimeSpan -Hours 1)
Register-ScheduledTask -TaskName "Disk Space Monitor" -Action $action -Trigger $trigger -User "SYSTEM"

Combine with log backup jobs to prevent recurrence:

-- T-SQL to check log space usage
DECLARE @logSpace TABLE (
    DBName NVARCHAR(128),
    LogSizeMB FLOAT,
    LogSpaceUsedPct FLOAT,
    Status INT
)

INSERT INTO @logSpace
EXEC('DBCC SQLPERF(LOGSPACE)')

SELECT * FROM @logSpace
WHERE LogSpaceUsedPct > 70 -- Warning threshold
ORDER BY LogSpaceUsedPct DESC