Lesson Learned #402:Automated Monitoring of Table Growth in SQL Databases Using PowerShell

[Jose_Manuel_Jurado]

Uncontrolled table growth in a SQL database can have a significant impact on system performance and efficiency. It is crucial for database administrators to monitor and detect significant changes in table size and row count to take timely corrective actions. In this article, we will introduce a PowerShell script that automates the process of monitoring table growth and provides alert notifications when predefined thresholds are exceeded.

Motivation:

Managing table growth is an essential task for database administrators to ensure optimal database performance. By regularly monitoring table size and row count, administrators can proactively identify tables that are growing rapidly and take necessary actions such as optimizing queries, archiving or partitioning data, or allocating additional storage resources.

However, manually tracking table growth across multiple databases and tables can be time-consuming and error-prone. Automating this process using a PowerShell script provides an efficient and reliable solution. The script connects to SQL databases, retrieves table information, compares it with previous records, and generates alerts and reports based on predefined thresholds.

Script Overview:

The PowerShell script follows a step-by-step process to monitor table growth in SQL databases. Let's take a closer look at the variables and code used in each step:

1. Database Connection:

   • $serverName: Specifies the SQL server name.
   • $databaseUser: Specifies the username for connecting to the database.
   • $databasePassword: Specifies the password for connecting to the database.
   • The script establishes a connection to the SQL server using the provided credentials.

2. Database Selection:

   • $specificDatabase: Specifies the name of a specific database to monitor. Leave it empty to monitor all databases.
   • The script retrieves the list of online databases from the server, with an option to filter by a specific database if required.

3. Table Retrieval:

   • $specificTable: Specifies the name of a specific table to monitor. Leave it empty to monitor all tables.
   • $specificSchema: Specifies the name of a specific schema to filter the tables. Leave it empty to monitor all schemas.
   • The script retrieves the list of tables from each selected database, considering optional filters for specific tables and schemas.

4. Comparison with Previous Results:

   • $sizeGrowthThreshold: Specifies the threshold (in percentage) for table size growth. If the growth exceeds this threshold, an alert will be triggered.
   • $rowGrowthThreshold: Specifies the threshold (in percentage) for row count growth. If the growth exceeds this threshold, it will be recorded.
   • The script compares the current table size and row count with the previously recorded values. If the growth exceeds the thresholds, it triggers an alert.

5. Alerting and Reporting:

   • $emailConfig: Specifies the email configuration parameters, including SMTP server details, sender and recipient email addresses, and subject.
   • The script sends an email notification to specified recipients when significant growth is detected, providing details of the table and growth percentage.

6. Logging:

   • $logFile: Specifies the path to the log file.
   • $maxLogFileSize: Specifies the maximum size of the log file in bytes.
   • The script logs the table comparisons and growth details in a log file for future reference.

7. JSON Storage:

   • $resultsFile: Specifies the path to the JSON file for storing previous table results.
   • The script stores the previous table results in a JSON file to track historical data for future comparisons.

Conclusion:

Automating the monitoring of table growth in SQL databases using PowerShell empowers database administrators to proactively manage and optimize database performance. By implementing this script, administrators can efficiently track table growth, identify problematic tables, and take necessary actions to ensure the stability and scalability of the database system.

With regular monitoring and timely interventions, organizations can maintain optimal performance and prevent potential issues resulting from uncontrolled table growth.

By using this script as a part of their database management toolkit, administrators can streamline their monitoring processes, improve efficiency, and ensure the long-term health of their SQL databases.

Feel free to modify and customize the code. This is an example for automation.

PowerShell Script:

# Database connection parameters
$serverName = "tcp:servername.database.windows.net,1433"
$databaseUser = "username"
$databasePassword = "password"

# Email configuration parameters
$emailConfig = @{
    SmtpServer = "<smtp_server>"
    Port = 587
    EnableSsl = $true
    Credential = [System.Net.NetworkCredential]::new("<sender_email>", "<sender_password>")
    From = "<sender_email>"
    To = "<recipient_email>"
    Subject = "Table Growth Alert"
}

# Growth threshold parameters (in percentage)
$sizeGrowthThreshold = 20
$rowGrowthThreshold = 30

# Path to JSON file for storing results
$resultsFile = "c:\temp\results.json"

# Path to log file
$logFile = "c:\temp\log.log"

# Maximum log file size in bytes
$maxLogFileSize = 10MB

# Number of connection retry attempts
$retryCount = 3

# Delay between retry attempts (in seconds)
$retryDelay = 5

# Specific database to check
$specificDatabase = "MyDB"

# Specific table to check (leave empty to check all tables)
$specificTable = "MyTable"

# Specific schema to check (leave empty to check all schemas)
$specificSchema = ""

# Get size and row information of a specific table
function GetTableInfo($database, $schemaName, $tableName, $connection) {
    $query = @"
SELECT 
    SUM(reserved_page_count) * 8 / 1024 AS TotalSizeMB,
    SUM(row_count) AS TotalRows
FROM 
    sys.dm_db_partition_stats
WHERE 
    object_id = OBJECT_ID('$schemaName.$tableName')
GROUP BY 
    object_id
"@

    $command = $connection.CreateCommand()
    $command.CommandText = $query
    $result = $command.ExecuteReader()

    if ($result.Read()) {
        $currentSizeMB = $result.GetValue(0)
        $currentRows = $result.GetValue(1)

        $result.Close()

        return @{
            TotalSizeMB = $currentSizeMB
            TotalRows = $currentRows
        }
    }

    $result.Close()

    return $null
}

# Function to send alert email
function SendAlertEmail($serverName, $database, $tableName, $growthPercentage, $currentSizeMB, $currentRows) {
    $emailBody = @"
The table $tableName in database $database on server $serverName has experienced a growth of $growthPercentage%. The current size is $currentSizeMB MB and the number of rows is $currentRows.
"@
    Send-MailMessage @emailConfig -Body $emailBody
}

try {
    # Create a timestamp for the current execution
    $timestamp = Get-Date -Format "yyyy-MM-dd HH:mm:ss"

    # Write timestamp to the log file
    Add-Content -Path $logFile -Value "Execution timestamp: $timestamp"

    # Check if log file exceeds the maximum size
    if ((Get-Item $logFile).Length -gt $maxLogFileSize) {
        # Generate a backup copy of the log file
        $backupLogFile = $logFile.Replace(".log", "_backup.log")
        Copy-Item -Path $logFile -Destination $backupLogFile

        # Create a new empty log file
        $null | Set-Content -Path $logFile
    }

    # Read previous results (if exist)
    $previousResults = @{}
    if (Test-Path $resultsFile) {
        $previousResultsJson = Get-Content $resultsFile | Out-String | ConvertFrom-Json
        foreach ($key in $previousResultsJson.PSObject.Properties.Name) {
            $previousResults[$key] = $previousResultsJson.$key
        }
    }

    # Connect to the database server and retrieve the list of databases
    $connectionString = "Server=$serverName;Database=master;User Id=$databaseUser;Password=$databasePassword;Integrated Security=False;"
    $masterConnection = New-Object System.Data.SqlClient.SqlConnection($connectionString)
    $masterConnection.Open()

    # Filter databases based on specific database variable
    $databasesQuery = "SELECT name FROM sys.databases WHERE state_desc = 'ONLINE' AND database_id >= 5"
    if ($specificDatabase) {
        $databasesQuery += " AND name = '$specificDatabase'"
    }

    $databasesCommand = $masterConnection.CreateCommand()
    $databasesCommand.CommandText = $databasesQuery
    $databases = $databasesCommand.ExecuteReader()

    # Store database names in an array
    $databaseNames = @()

    while ($databases.Read()) {
        $databaseName = $databases.GetString(0)
        $databaseNames += $databaseName
    }

    $databases.Close()
    $masterConnection.Close()

    # Process tables in each database
    foreach ($databaseName in $databaseNames) {
        # Connect to the current database
        $databaseConnectionString = "Server=$serverName;Database=$databaseName;User Id=$databaseUser;Password=$databasePassword;Integrated Security=False;"
        $databaseConnection = New-Object System.Data.SqlClient.SqlConnection($databaseConnectionString)

        $retryAttempts = 0
        $connected = $false

        while (-not $connected -and $retryAttempts -lt $retryCount) {
            try {
                $databaseConnection.Open()
                $connected = $true
            }
            catch {
                $retryAttempts++
                Write-Host "Error connecting to database $databaseName. Retrying in $retryDelay seconds..."
                Start-Sleep -Seconds $retryDelay
            }
        }

        if ($connected) {
            # Get the list of tables
            $tablesQuery = "SELECT schema_name(schema_id) AS schema_name, name FROM sys.tables WHERE is_ms_shipped = 0"
            if ($specificSchema) {
                $tablesQuery += " AND schema_name = '$specificSchema'"
            }
            if ($specificTable) {
                $tablesQuery += " AND name = '$specificTable'"
            }
            $tablesQuery += " ORDER BY name"

            $tablesCommand = $databaseConnection.CreateCommand()
            $tablesCommand.CommandText = $tablesQuery
            $tables = $tablesCommand.ExecuteReader()

            $tablesData = New-Object System.Collections.ArrayList

            while ($tables.Read()) {
                $schemaName = $tables.GetString(0)
                $tableName = $tables.GetString(1)
                $tableData = @{
                    SchemaName = $schemaName
                    TableName = $tableName
                }
                $tablesData.Add($tableData) | Out-Null
            }

            $tables.Close()

            foreach ($tableData in $tablesData) {
                $schemaName = $tableData.SchemaName
                $tableName = $tableData.TableName
                $tableKey = "$serverName|$databaseName|$schemaName|$tableName"

                # Check if previous results exist for comparison
                if ($previousResults.ContainsKey($tableKey)) {
                    $previousSizeMB = $previousResults[$tableKey].TotalSizeMB
                    $previousRows = $previousResults[$tableKey].TotalRows

                    # Get the current table information
                    $currentTableInfo = GetTableInfo $databaseName $schemaName $tableName $databaseConnection

                    if ($currentTableInfo) {
                        $currentSizeMB = $currentTableInfo.TotalSizeMB
                        $currentRows = $currentTableInfo.TotalRows

                        # Calculate the growth percentage
                        $growthPercentage = 0
                        $rowGrowthPercentage = 0

                        if ($previousSizeMB -ne 0) {
                            $growthPercentage = ($currentSizeMB / $previousSizeMB - 1) * 100
                        }

                        if ($previousRows -ne 0) {
                            $rowGrowthPercentage = ($currentRows / $previousRows - 1) * 100
                        }

                        # Check if either size or row growth exceeds the threshold
                        if ($growthPercentage -ge $sizeGrowthThreshold -or $rowGrowthPercentage -ge $rowGrowthThreshold) {
                            # Send alert email
                            SendAlertEmail $serverName $databaseName $tableName $growthPercentage $currentSizeMB $currentRows
                        }

                        # Update the current results in the previous results
                        $previousResults[$tableKey].TotalSizeMB = $currentSizeMB
                        $previousResults[$tableKey].TotalRows = $currentRows
                    }
                }
                else {
                    # Store the current results
                    $currentTableInfo = GetTableInfo $databaseName $schemaName $tableName $databaseConnection

                    if ($currentTableInfo) {
                        $previousResults[$tableKey] = $currentTableInfo
                    }
                }

                # Log the table comparison
                $logMessage = "Database: $databaseName, Table: $schemaName.$tableName, Size Growth: $growthPercentage%, Rows Growth: $rowGrowthPercentage%"
                Add-Content -Path $logFile -Value $logMessage
            }

            $databaseConnection.Close()
        }
        else {
            Write-Host "Failed to establish a connection to database $databaseName after $retryCount attempts."
        }
    }

    # Save the results to the JSON file
    $previousResultsJson = $previousResults | ConvertTo-Json -Depth 10
    $previousResultsJson | Set-Content $resultsFile

    # Execution completed successfully
    Write-Host "Table growth analysis completed successfully."

}
catch {
    # Execution encountered an error
    Write-Host "An error occurred while running the script: $($_.Exception.Message)"
}