Database-IndexMaintenance.ps1

<#
.SYNOPSIS
    Automated SQL Server index maintenance with logging.

.DESCRIPTION
    Identifies fragmented indexes across databases and performs
    REORGANIZE or REBUILD operations based on configurable thresholds.
    Logs all actions and sends summary email on completion.

.PARAMETER ServerInstance
    SQL Server instance name.

.PARAMETER ReorgThreshold
    Fragmentation percentage to trigger REORGANIZE (default 5).

.PARAMETER RebuildThreshold
    Fragmentation percentage to trigger REBUILD (default 30).

.PARAMETER LogPath
    Path to write the maintenance log file.

.EXAMPLE
    .\Database-IndexMaintenance.ps1 -ServerInstance "SQLSERVER01" -LogPath "D:\Logs"
#>

[CmdletBinding()]
param(
    [Parameter(Mandatory)]
    [string]$ServerInstance,

    [double]$ReorgThreshold = 5.0,
    [double]$RebuildThreshold = 30.0,
    [int]$MinPageCount = 1000,

    [string]$LogPath = "D:\DBA\Logs"
)

$ErrorActionPreference = "Stop"
$timestamp = Get-Date -Format "yyyyMMdd_HHmmss"
$logFile = Join-Path $LogPath "IndexMaintenance_$timestamp.log"

function Write-Log {
    param([string]$Message, [string]$Level = "INFO")
    $entry = "$(Get-Date -Format 'yyyy-MM-dd HH:mm:ss') [$Level] $Message"
    Add-Content -Path $logFile -Value $entry
    Write-Host $entry
}

# Ensure log directory exists
if (-not (Test-Path $LogPath)) {
    New-Item -ItemType Directory -Path $LogPath -Force | Out-Null
}

Write-Log "Index maintenance started on $ServerInstance"
Write-Log "Thresholds: Reorg=$ReorgThreshold%, Rebuild=$RebuildThreshold%, MinPages=$MinPageCount"

$totalReorg = 0
$totalRebuild = 0
$totalErrors = 0

try {
    # Get list of user databases
    $databases = Invoke-Sqlcmd -ServerInstance $ServerInstance -Query "
        SELECT name FROM sys.databases
        WHERE database_id > 4 AND state = 0 AND is_read_only = 0
    "

    foreach ($db in $databases) {
        $dbName = $db.name
        Write-Log "Processing database: $dbName"

        # Get fragmented indexes
        $query = @"
            SELECT
                s.name AS SchemaName,
                t.name AS TableName,
                i.name AS IndexName,
                ips.avg_fragmentation_in_percent AS Fragmentation,
                ips.page_count AS PageCount
            FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
            INNER JOIN sys.tables t ON ips.object_id = t.object_id
            INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
            INNER JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
            WHERE ips.avg_fragmentation_in_percent > $ReorgThreshold
              AND ips.page_count > $MinPageCount
              AND i.name IS NOT NULL
              AND ips.index_id > 0
            ORDER BY ips.avg_fragmentation_in_percent DESC
"@

        $indexes = Invoke-Sqlcmd -ServerInstance $ServerInstance -Database $dbName -Query $query

        foreach ($idx in $indexes) {
            $schema = $idx.SchemaName
            $table = $idx.TableName
            $index = $idx.IndexName
            $frag = [math]::Round($idx.Fragmentation, 1)

            try {
                if ($frag -gt $RebuildThreshold) {
                    $action = "REBUILD"
                    $sql = "ALTER INDEX [$index] ON [$schema].[$table] REBUILD WITH (ONLINE = OFF, SORT_IN_TEMPDB = ON)"
                }
                else {
                    $action = "REORGANIZE"
                    $sql = "ALTER INDEX [$index] ON [$schema].[$table] REORGANIZE"
                }

                $stopwatch = [System.Diagnostics.Stopwatch]::StartNew()
                Invoke-Sqlcmd -ServerInstance $ServerInstance -Database $dbName -Query $sql -QueryTimeout 3600
                $stopwatch.Stop()

                $duration = $stopwatch.ElapsedMilliseconds
                Write-Log "$action : $dbName.$schema.$table.$index (Frag: $frag%, Duration: ${duration}ms)"

                if ($action -eq "REBUILD") { $totalRebuild++ } else { $totalReorg++ }
            }
            catch {
                $totalErrors++
                Write-Log "ERROR on $dbName.$schema.$table.$index : $_" -Level "ERROR"
            }
        }
    }
}
catch {
    Write-Log "FATAL: $_" -Level "ERROR"
    throw
}

Write-Log "Index maintenance completed. Reorganized: $totalReorg, Rebuilt: $totalRebuild, Errors: $totalErrors"