sp_TransactionLogHealthCheckup.sql

/*******************************************************************************
Transaction Log Health Checkup
Version: 1.4
Date: 2026-01-22
Author: Kieran Cremin

Description: One-time diagnostic tool that analyzes transaction log health across
             all databases and identifies immediate issues requiring attention.

Resource Links:
  SQL Server transaction log architecture						https://www.red-gate.com/simple-talk/databases/sql-server/learn/sql-server-transaction-log-architecture/
  A Busy/Accidental DBA’s Guide to Managing VLFs				https://adventuresinsql.com/2009/12/a-busyaccidental-dbas-guide-to-managing-vlfs/
  Manage the size of the transaction log file					https://learn.microsoft.com/en-us/sql/relational-databases/logs/manage-the-size-of-the-transaction-log-file?view=sql-server-ver17
  SQL Server Transaction Log Administration Best Practices		https://www.sqlshack.com/sql-server-transaction-log-administration-best-practices/
  My Favorite System Column: LOG_REUSE_WAIT_DESC				https://www.brentozar.com/archive/2016/03/my-favorite-system-column-log_reuse_wait_desc/
  DBCC OPENTRAN in SQL Server									https://stedmansolutions.com/2025/02/28/dbcc-opentran-in-sql-server/
  Microsoft SQL Server Transaction Log Backup					https://www.msp360.com/resources/blog/sql-server-transaction-log-backup/

Output Format:
  Issues and Recommendations columns are returned as XML clickable links in SSMS.
  Click the blue hyperlink to view formatted content in a separate window.

v1.4 Changes: Convert Issues and Recommendations columns to XML type for better
              viewing in SSMS. Shows as clickable blue hyperlinks that open in
              formatted viewer window. No need to expand cells manually.
v1.3 Changes: Added check for log backups older than 15 minutes. Suggests more
              frequent backups (every 15-30 mins) for FULL recovery databases when
              last backup was >15 mins but <4 hours ago. Helps identify backup
              frequency issues even without time-series data.
v1.2 Changes: Format Issues and Recommendations columns with line breaks for better
              readability. Added placeholder resource links for further reading.
v1.1 Changes: Fixed autogrowth assessment logic to only suggest larger autogrowth
              for large logs when ratio is healthy. Don't flag autogrowth issues
              on bloated logs that need shrinking anyway.

What It Checks:
  ✅ VLF fragmentation (Virtual Log Files count)
  ✅ Database-to-Log size ratio (identify oversized logs)
  ✅ Autogrowth configuration (percent vs fixed, size appropriateness)
  ✅ Log backup frequency (for FULL recovery databases)
  ✅ Log space usage (current utilization)
  ✅ CDC/Replication/AG status
  ✅ Long-running transactions
  ✅ Log reuse wait conditions

Issues Identified:
  🔴 CRITICAL: VLF count > 1000, log backup missing 24+ hours, log >100% of data
  ⚠️  WARNING: VLF count 500-1000, log backup 12-24 hours, log 50-100% of data
  ℹ️  INFO: Suboptimal autogrowth settings, log 25-50% of data

Output:
  - Executive summary with issue counts
  - Per-database health report with priority levels
  - Specific recommendations for each database
  - Optional: Ready-to-execute optimization scripts

Usage:
  -- Quick checkup (all databases)
  EXEC [master].[dbo].[sp_TransactionLogHealthCheckup];

  -- Checkup for specific database
  EXEC [master].[dbo].[sp_TransactionLogHealthCheckup] @DatabaseName = 'YourDB';

  -- Show only databases with issues
  EXEC [master].[dbo].[sp_TransactionLogHealthCheckup] @ShowHealthyDatabases = 0;

  -- Generate optimization scripts
  EXEC [master].[dbo].[sp_TransactionLogHealthCheckup] @GenerateScripts = 1;

*******************************************************************************/

USE [master];
GO

IF OBJECT_ID('dbo.sp_TransactionLogHealthCheckup', 'P') IS NOT NULL
    DROP PROCEDURE [dbo].[sp_TransactionLogHealthCheckup];
GO

CREATE PROCEDURE [dbo].[sp_TransactionLogHealthCheckup]
    @DatabaseName SYSNAME = NULL,           -- Specific database or NULL for all
    @ShowHealthyDatabases BIT = 1,          -- Show databases with no issues
    @GenerateScripts BIT = 0                -- Generate optimization T-SQL scripts
AS
BEGIN
    SET NOCOUNT ON;

    -- Temp table for health results
    CREATE TABLE #LogHealth (
        DatabaseName SYSNAME,
        RecoveryModel VARCHAR(20),
        DatabaseSizeMB DECIMAL(18,2),
        LogSizeMB DECIMAL(18,2),
        LogToDataRatio DECIMAL(10,4),
        LogUsedMB DECIMAL(18,2),
        LogUsedPercent DECIMAL(5,2),
        VLFCount INT,
        AutogrowthMB INT NULL,
        IsPercentGrowth BIT,
        MaxSizeMB INT NULL,
        LastLogBackupTime DATETIME NULL,
        HoursSinceLastLogBackup DECIMAL(10,2) NULL,
        HasCDC BIT,
        HasReplication BIT,
        HasAlwaysOn BIT,
        LogReuseWaitDesc VARCHAR(60),
        ActiveTransactionCount INT,
        OldestActiveTranMinutes INT,
        -- Health Assessment
        HealthStatus VARCHAR(20),
        Priority INT,
        VLFHealthStatus VARCHAR(20),
        RatioHealthStatus VARCHAR(20),
        BackupHealthStatus VARCHAR(20),
        AutogrowthHealthStatus VARCHAR(20),
        Issues NVARCHAR(MAX),
        Recommendations NVARCHAR(MAX)
    );

    -- Variables for cursor
    DECLARE @CurrentDB SYSNAME;
    DECLARE @SQL NVARCHAR(MAX);
    DECLARE @VLFCount INT;

    -- Temp table for VLF counts
    CREATE TABLE #VLFCounts (
        DatabaseName SYSNAME,
        VLFCount INT
    );

    -- Temp table for log usage
    CREATE TABLE #LogUsage (
        DatabaseName SYSNAME,
        UsedLogSpaceInBytes BIGINT,
        UsedLogSpaceInPercent DECIMAL(5,2)
    );

    -- Temp table for active transactions
    CREATE TABLE #ActiveTransactions (
        DatabaseName SYSNAME,
        ActiveTranCount INT,
        OldestTranMinutes INT
    );

    -- Collect VLF count and log usage for each database
    DECLARE db_cursor CURSOR LOCAL FAST_FORWARD FOR
        SELECT d.name
        FROM sys.databases d
        LEFT JOIN sys.dm_hadr_database_replica_states hdrs
            ON d.database_id = hdrs.database_id
            AND hdrs.is_local = 1
        WHERE d.database_id > 4  -- Exclude system databases
            AND d.state_desc = 'ONLINE'
            AND d.is_read_only = 0
            AND (@DatabaseName IS NULL OR d.name = @DatabaseName)
            AND (
                hdrs.database_id IS NULL  -- Not in AG
                OR hdrs.is_primary_replica = 1  -- Or PRIMARY replica
            );

    OPEN db_cursor;
    FETCH NEXT FROM db_cursor INTO @CurrentDB;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        BEGIN TRY
            -- Get VLF count
            SET @SQL = N'SELECT @VLFCountOUT = COUNT(*) FROM sys.dm_db_log_info(DB_ID(''' + @CurrentDB + '''))';
            EXEC sp_executesql @SQL, N'@VLFCountOUT INT OUTPUT', @VLFCountOUT = @VLFCount OUTPUT;

            INSERT INTO #VLFCounts (DatabaseName, VLFCount)
            VALUES (@CurrentDB, @VLFCount);

            -- Get log usage (must be in database context)
            SET @SQL = N'
            USE [' + @CurrentDB + N'];
            SELECT
                ''' + @CurrentDB + N''' AS DatabaseName,
                used_log_space_in_bytes,
                used_log_space_in_percent
            FROM sys.dm_db_log_space_usage;';

            INSERT INTO #LogUsage
            EXEC sp_executesql @SQL;

            -- Get active transaction info
            SET @SQL = N'
            USE [' + @CurrentDB + N'];
            SELECT
                ''' + @CurrentDB + N''' AS DatabaseName,
                COUNT(*) AS ActiveTranCount,
                MAX(DATEDIFF(MINUTE, transaction_begin_time, GETDATE())) AS OldestTranMinutes
            FROM sys.dm_tran_active_transactions t
            INNER JOIN sys.dm_tran_session_transactions st ON t.transaction_id = st.transaction_id
            WHERE transaction_type != 2
                AND transaction_begin_time IS NOT NULL;';

            INSERT INTO #ActiveTransactions
            EXEC sp_executesql @SQL;

        END TRY
        BEGIN CATCH
            PRINT 'Error collecting metrics for database: ' + @CurrentDB;
        END CATCH

        FETCH NEXT FROM db_cursor INTO @CurrentDB;
    END

    CLOSE db_cursor;
    DEALLOCATE db_cursor;

    -- Collect all metrics
    INSERT INTO #LogHealth (
        DatabaseName,
        RecoveryModel,
        DatabaseSizeMB,
        LogSizeMB,
        LogToDataRatio,
        LogUsedMB,
        LogUsedPercent,
        VLFCount,
        AutogrowthMB,
        IsPercentGrowth,
        MaxSizeMB,
        LastLogBackupTime,
        HoursSinceLastLogBackup,
        HasCDC,
        HasReplication,
        HasAlwaysOn,
        LogReuseWaitDesc,
        ActiveTransactionCount,
        OldestActiveTranMinutes
    )
    SELECT
        d.name AS DatabaseName,
        d.recovery_model_desc AS RecoveryModel,
        -- Database size
        CAST(data_size.TotalDataSizeMB AS DECIMAL(18,2)) AS DatabaseSizeMB,
        -- Log size
        CAST(log_mf.size * 8.0 / 1024 AS DECIMAL(18,2)) AS LogSizeMB,
        -- Log-to-Data ratio
        CASE
            WHEN data_size.TotalDataSizeMB > 0 THEN
                CAST((log_mf.size * 8.0 / 1024) / data_size.TotalDataSizeMB AS DECIMAL(10,4))
            ELSE NULL
        END AS LogToDataRatio,
        -- Log usage
        CAST(lu.UsedLogSpaceInBytes / 1024.0 / 1024 AS DECIMAL(18,2)) AS LogUsedMB,
        CAST(lu.UsedLogSpaceInPercent AS DECIMAL(5,2)) AS LogUsedPercent,
        -- VLF count
        ISNULL(vlf.VLFCount, 0) AS VLFCount,
        -- Autogrowth
        CASE
            WHEN log_mf.is_percent_growth = 0 THEN log_mf.growth * 8 / 1024
            ELSE NULL
        END AS AutogrowthMB,
        log_mf.is_percent_growth AS IsPercentGrowth,
        CASE
            WHEN log_mf.max_size = -1 THEN NULL  -- Unlimited
            WHEN log_mf.max_size = 268435456 THEN NULL  -- Default max (2TB)
            ELSE log_mf.max_size * 8 / 1024
        END AS MaxSizeMB,
        -- Backup info
        bs.backup_finish_date AS LastLogBackupTime,
        CAST(DATEDIFF(MINUTE, bs.backup_finish_date, GETDATE()) / 60.0 AS DECIMAL(10,2)) AS HoursSinceLastLogBackup,
        -- Feature flags
        CASE WHEN d.is_cdc_enabled = 1 THEN 1 ELSE 0 END AS HasCDC,
        CASE WHEN d.is_published = 1 OR d.is_subscribed = 1 THEN 1 ELSE 0 END AS HasReplication,
        CASE WHEN hdrs.database_id IS NOT NULL THEN 1 ELSE 0 END AS HasAlwaysOn,
        -- Log reuse wait
        d.log_reuse_wait_desc AS LogReuseWaitDesc,
        -- Active transactions
        ISNULL(at.ActiveTranCount, 0) AS ActiveTransactionCount,
        ISNULL(at.OldestTranMinutes, 0) AS OldestActiveTranMinutes
    FROM sys.databases d
    INNER JOIN sys.master_files log_mf
        ON d.database_id = log_mf.database_id
        AND log_mf.type = 1  -- Log files
    LEFT JOIN #LogUsage lu
        ON d.name = lu.DatabaseName
    CROSS APPLY (
        SELECT SUM(size * 8.0 / 1024) AS TotalDataSizeMB
        FROM sys.master_files
        WHERE database_id = d.database_id
        AND type = 0  -- Data files
    ) AS data_size
    LEFT JOIN #VLFCounts vlf
        ON d.name = vlf.DatabaseName
    LEFT JOIN #ActiveTransactions at
        ON d.name = at.DatabaseName
    LEFT OUTER JOIN (
        SELECT
            database_name,
            MAX(backup_finish_date) AS backup_finish_date
        FROM msdb.dbo.backupset
        WHERE type = 'L'  -- Log backups
        GROUP BY database_name
    ) bs ON d.name = bs.database_name
    LEFT JOIN sys.dm_hadr_database_replica_states hdrs
        ON d.database_id = hdrs.database_id
        AND hdrs.is_local = 1
    WHERE d.database_id > 4
        AND d.state_desc = 'ONLINE'
        AND d.is_read_only = 0
        AND (@DatabaseName IS NULL OR d.name = @DatabaseName)
        AND (
            hdrs.database_id IS NULL
            OR hdrs.is_primary_replica = 1
        );

    -- Analyze health and assign statuses
    UPDATE #LogHealth
    SET
        -- VLF Health
        VLFHealthStatus = CASE
            WHEN VLFCount > 1000 THEN 'CRITICAL'
            WHEN VLFCount > 500 THEN 'WARNING'
            WHEN VLFCount > 200 THEN 'INFO'
            ELSE 'GOOD'
        END,
        -- Ratio Health
        RatioHealthStatus = CASE
            WHEN LogToDataRatio > 1.0 THEN 'CRITICAL'
            WHEN LogToDataRatio > 0.5 THEN 'WARNING'
            WHEN LogToDataRatio > 0.25 THEN 'INFO'
            ELSE 'GOOD'
        END,
        -- Backup Health (only for FULL recovery)
        BackupHealthStatus = CASE
            WHEN RecoveryModel = 'FULL' AND LastLogBackupTime IS NULL THEN 'CRITICAL'
            WHEN RecoveryModel = 'FULL' AND HoursSinceLastLogBackup > 24 THEN 'CRITICAL'
            WHEN RecoveryModel = 'FULL' AND HoursSinceLastLogBackup > 12 THEN 'WARNING'
            WHEN RecoveryModel = 'FULL' AND HoursSinceLastLogBackup > 4 THEN 'INFO'
            WHEN RecoveryModel = 'FULL' AND HoursSinceLastLogBackup > 0.25 THEN 'INFO'  -- >15 mins
            ELSE 'GOOD'
        END,
        -- Autogrowth Health
        AutogrowthHealthStatus = CASE
            WHEN IsPercentGrowth = 1 THEN 'WARNING'  -- Percent growth is suboptimal
            WHEN AutogrowthMB IS NOT NULL AND AutogrowthMB < 64 THEN 'WARNING'  -- Too small
            WHEN AutogrowthMB IS NOT NULL AND LogSizeMB > 10000 AND AutogrowthMB < 512 AND LogToDataRatio <= 0.5 THEN 'INFO'  -- Large log with small growth (only if not bloated)
            ELSE 'GOOD'
        END;

    -- Calculate overall priority
    UPDATE #LogHealth
    SET Priority = CASE
        WHEN VLFHealthStatus = 'CRITICAL' OR RatioHealthStatus = 'CRITICAL' OR BackupHealthStatus = 'CRITICAL' THEN 1
        WHEN VLFHealthStatus = 'WARNING' OR RatioHealthStatus = 'WARNING' OR BackupHealthStatus = 'WARNING' OR AutogrowthHealthStatus = 'WARNING' THEN 2
        WHEN VLFHealthStatus = 'INFO' OR RatioHealthStatus = 'INFO' OR BackupHealthStatus = 'INFO' OR AutogrowthHealthStatus = 'INFO' THEN 3
        ELSE 4
    END;

    -- Set overall health status
    UPDATE #LogHealth
    SET HealthStatus = CASE Priority
        WHEN 1 THEN 'CRITICAL'
        WHEN 2 THEN 'WARNING'
        WHEN 3 THEN 'INFO'
        ELSE 'HEALTHY'
    END;

    -- Build issues list (with line breaks and resource links)
    UPDATE #LogHealth
    SET Issues =
        CASE WHEN VLFHealthStatus = 'CRITICAL' THEN
            '🔴 VLF count critically high (' + CAST(VLFCount AS VARCHAR(10)) + ')' + CHAR(10) +
            '   ℹ️ Info: https://adventuresinsql.com/2009/12/a-busyaccidental-dbas-guide-to-managing-vlfs/' + CHAR(10)
        ELSE '' END +
        CASE WHEN VLFHealthStatus = 'WARNING' THEN
            '⚠️ VLF count high (' + CAST(VLFCount AS VARCHAR(10)) + ')' + CHAR(10) +
            '   ℹ️ Info: https://adventuresinsql.com/2009/12/a-busyaccidental-dbas-guide-to-managing-vlfs/' + CHAR(10)
        ELSE '' END +
        CASE WHEN VLFHealthStatus = 'INFO' THEN
            'ℹ️ VLF count elevated (' + CAST(VLFCount AS VARCHAR(10)) + ')' + CHAR(10)
        ELSE '' END +
        CASE WHEN RatioHealthStatus = 'CRITICAL' THEN
            '🔴 Log larger than database (' + CAST(CAST(LogToDataRatio * 100 AS INT) AS VARCHAR(10)) + '%)' + CHAR(10) +
            '   ℹ️ Info: https://learn.microsoft.com/en-us/sql/relational-databases/logs/manage-the-size-of-the-transaction-log-file?view=sql-server-ver17' + CHAR(10)
        ELSE '' END +
        CASE WHEN RatioHealthStatus = 'WARNING' THEN
            '⚠️ High log-to-data ratio (' + CAST(CAST(LogToDataRatio * 100 AS INT) AS VARCHAR(10)) + '%)' + CHAR(10) +
            '   ℹ️ Info: https://learn.microsoft.com/en-us/sql/relational-databases/logs/manage-the-size-of-the-transaction-log-file?view=sql-server-ver17' + CHAR(10)
        ELSE '' END +
        CASE WHEN RatioHealthStatus = 'INFO' THEN
            'ℹ️ Moderate log-to-data ratio (' + CAST(CAST(LogToDataRatio * 100 AS INT) AS VARCHAR(10)) + '%)' + CHAR(10)
        ELSE '' END +
        CASE WHEN BackupHealthStatus = 'CRITICAL' AND LastLogBackupTime IS NULL THEN
            '🔴 No log backups found' + CHAR(10) +
            '   ℹ️ Info: https://www.sqlshack.com/sql-server-transaction-log-administration-best-practices/' + CHAR(10)
        ELSE '' END +
        CASE WHEN BackupHealthStatus = 'CRITICAL' AND HoursSinceLastLogBackup > 24 THEN
            '🔴 Log backup overdue (' + CAST(CAST(HoursSinceLastLogBackup AS INT) AS VARCHAR(10)) + 'h ago)' + CHAR(10) +
            '   ℹ️ Info: https://www.sqlshack.com/sql-server-transaction-log-administration-best-practices/' + CHAR(10)
        ELSE '' END +
        CASE WHEN BackupHealthStatus = 'WARNING' THEN
            '⚠️ Log backup aging (' + CAST(CAST(HoursSinceLastLogBackup AS INT) AS VARCHAR(10)) + 'h ago)' + CHAR(10)
        ELSE '' END +
        CASE WHEN RecoveryModel = 'FULL' AND HoursSinceLastLogBackup > 0.25 AND HoursSinceLastLogBackup <= 4 THEN
            'ℹ️ Consider more frequent log backups (' + CAST(CAST(HoursSinceLastLogBackup * 60 AS INT) AS VARCHAR(10)) + ' mins ago)' + CHAR(10) +
            '   ℹ️ Info: https://www.msp360.com/resources/blog/sql-server-transaction-log-backup/' + CHAR(10)
        ELSE '' END +
        CASE WHEN AutogrowthHealthStatus = 'WARNING' AND IsPercentGrowth = 1 THEN
            '⚠️ Percent-based autogrowth' + CHAR(10) +
            '   ℹ️ Info: https://learn.microsoft.com/en-us/sql/relational-databases/logs/manage-the-size-of-the-transaction-log-file?view=sql-server-ver17' + CHAR(10)
        ELSE '' END +
        CASE WHEN AutogrowthHealthStatus = 'WARNING' AND AutogrowthMB < 64 THEN
            '⚠️ Autogrowth too small (' + CAST(AutogrowthMB AS VARCHAR(10)) + 'MB)' + CHAR(10)
        ELSE '' END +
        CASE WHEN LogReuseWaitDesc != 'NOTHING' AND LogReuseWaitDesc != 'CHECKPOINT' THEN
            '⚠️ Log reuse waiting on ' + LogReuseWaitDesc + CHAR(10) +
            '   ℹ️ Info: https://www.brentozar.com/archive/2016/03/my-favorite-system-column-log_reuse_wait_desc/' + CHAR(10)
        ELSE '' END +
        CASE WHEN OldestActiveTranMinutes > 60 THEN
            '⚠️ Long-running transaction (' + CAST(OldestActiveTranMinutes AS VARCHAR(10)) + ' mins)' + CHAR(10) +
            '   ℹ️ Info: https://stedmansolutions.com/2025/02/28/dbcc-opentran-in-sql-server/' + CHAR(10)
        ELSE '' END;

    -- Build recommendations (with line breaks)
    UPDATE #LogHealth
    SET Recommendations =
        CASE WHEN VLFCount > 500 THEN '• Rebuild log file to reduce VLF fragmentation' + CHAR(10) ELSE '' END +
        CASE WHEN LogToDataRatio > 0.5 THEN '• Review log sizing - may be oversized or data undersized' + CHAR(10) ELSE '' END +
        CASE WHEN BackupHealthStatus IN ('CRITICAL', 'WARNING') THEN '• Implement regular log backups (every 15-30 minutes for FULL recovery)' + CHAR(10) ELSE '' END +
        CASE WHEN RecoveryModel = 'FULL' AND HoursSinceLastLogBackup > 0.25 AND HoursSinceLastLogBackup <= 4 THEN '• Consider increasing log backup frequency to every 15-30 minutes' + CHAR(10) ELSE '' END +
        CASE WHEN IsPercentGrowth = 1 THEN '• Change autogrowth from percent to fixed MB (e.g., 512MB or 1024MB)' + CHAR(10) ELSE '' END +
        CASE WHEN AutogrowthMB < 64 THEN '• Increase autogrowth increment to at least 256MB' + CHAR(10) ELSE '' END +
        CASE WHEN LogReuseWaitDesc = 'REPLICATION' THEN '• Check CDC/Replication jobs - may be blocking log truncation' + CHAR(10) ELSE '' END +
        CASE WHEN OldestActiveTranMinutes > 60 THEN '• Investigate long-running transaction - may prevent log truncation' + CHAR(10) ELSE '' END;

    -- Cleanup temp tables
    DROP TABLE #VLFCounts;
    DROP TABLE #LogUsage;
    DROP TABLE #ActiveTransactions;

    -- Calculate summary counts
    DECLARE @TotalDatabases INT;
    DECLARE @CriticalCount INT;
    DECLARE @WarningCount INT;
    DECLARE @InfoCount INT;
    DECLARE @HealthyCount INT;

    SELECT
        @TotalDatabases = COUNT(*),
        @CriticalCount = SUM(CASE WHEN Priority = 1 THEN 1 ELSE 0 END),
        @WarningCount = SUM(CASE WHEN Priority = 2 THEN 1 ELSE 0 END),
        @InfoCount = SUM(CASE WHEN Priority = 3 THEN 1 ELSE 0 END),
        @HealthyCount = SUM(CASE WHEN Priority = 4 THEN 1 ELSE 0 END)
    FROM #LogHealth;

    -- Display report header
    PRINT '';
    PRINT '================================================================================';
    PRINT '              TRANSACTION LOG HEALTH CHECKUP REPORT';
    PRINT '================================================================================';
    PRINT 'Report Time: ' + CONVERT(VARCHAR(30), GETDATE(), 120);
    PRINT '';
    PRINT 'SUMMARY:';
    PRINT '  Total Databases Checked: ' + CAST(@TotalDatabases AS VARCHAR(10));
    PRINT '  🔴 Critical Issues: ' + CAST(@CriticalCount AS VARCHAR(10));
    PRINT '  ⚠️  Warnings: ' + CAST(@WarningCount AS VARCHAR(10));
    PRINT '  ℹ️  Info/Suboptimal: ' + CAST(@InfoCount AS VARCHAR(10));
    PRINT '  ✅ Healthy: ' + CAST(@HealthyCount AS VARCHAR(10));
    PRINT '';
    PRINT '================================================================================';
    PRINT '';

    -- Return detailed results
    IF @ShowHealthyDatabases = 1
    BEGIN
        SELECT
            DatabaseName,
            HealthStatus,
            VLFCount,
            VLFHealthStatus,
            DatabaseSizeMB,
            LogSizeMB,
            CAST(LogToDataRatio * 100 AS DECIMAL(10,2)) AS LogToDataRatioPct,
            RatioHealthStatus,
            LogUsedPercent,
            CASE WHEN IsPercentGrowth = 1
                THEN 'PERCENT'
                ELSE CAST(AutogrowthMB AS VARCHAR(20)) + ' MB'
            END AS Autogrowth,
            AutogrowthHealthStatus,
            HoursSinceLastLogBackup,
            BackupHealthStatus,
            LogReuseWaitDesc,
            HasCDC,
            HasReplication,
            HasAlwaysOn,
            ActiveTransactionCount,
            OldestActiveTranMinutes,
            CAST(N'<Issues>' + ISNULL(Issues, '') + N'</Issues>' AS XML) AS Issues,
            CAST(N'<Recommendations>' + ISNULL(Recommendations, '') + N'</Recommendations>' AS XML) AS Recommendations
        FROM #LogHealth
        ORDER BY Priority, DatabaseName;
    END
    ELSE
    BEGIN
        SELECT
            DatabaseName,
            HealthStatus,
            VLFCount,
            VLFHealthStatus,
            DatabaseSizeMB,
            LogSizeMB,
            CAST(LogToDataRatio * 100 AS DECIMAL(10,2)) AS LogToDataRatioPct,
            RatioHealthStatus,
            LogUsedPercent,
            CASE WHEN IsPercentGrowth = 1
                THEN 'PERCENT'
                ELSE CAST(AutogrowthMB AS VARCHAR(20)) + ' MB'
            END AS Autogrowth,
            AutogrowthHealthStatus,
            HoursSinceLastLogBackup,
            BackupHealthStatus,
            LogReuseWaitDesc,
            HasCDC,
            HasReplication,
            HasAlwaysOn,
            ActiveTransactionCount,
            OldestActiveTranMinutes,
            CAST(N'<Issues>' + ISNULL(Issues, '') + N'</Issues>' AS XML) AS Issues,
            CAST(N'<Recommendations>' + ISNULL(Recommendations, '') + N'</Recommendations>' AS XML) AS Recommendations
        FROM #LogHealth
        WHERE Priority < 4  -- Only show issues
        ORDER BY Priority, DatabaseName;
    END

    -- Generate optimization scripts if requested
    IF @GenerateScripts = 1
    BEGIN
        PRINT '';
        PRINT '================================================================================';
        PRINT '              OPTIMIZATION SCRIPTS';
        PRINT '================================================================================';
        PRINT '';
        PRINT '/*******************************************************************************';
        PRINT '                            ⚠️  IMPORTANT WARNING  ⚠️';
        PRINT '*******************************************************************************/';
        PRINT '/*';
        PRINT 'These scripts are RECOMMENDATIONS ONLY and must be reviewed before execution.';
        PRINT '';
        PRINT 'CRITICAL REQUIREMENTS BEFORE EXECUTION:';
        PRINT '  ✅ Review and customize for your specific environment';
        PRINT '  ✅ Test in non-production environment first';
        PRINT '  ✅ Schedule during approved maintenance window';
        PRINT '  ✅ Take full database backup before execution';
        PRINT '  ✅ Verify database is not in an Always On AG (or suspend sync first)';
        PRINT '  ✅ Ensure no active transactions or long-running queries';
        PRINT '  ✅ Understand each step and its impact before running';
        PRINT '';
        PRINT 'KNOWN RISKS AND IMPACTS:';
        PRINT '  ⚠️  VLF rebuild temporarily switches to SIMPLE recovery (breaks log chain)';
        PRINT '  ⚠️  DBCC SHRINKFILE causes high I/O and can block other operations';
        PRINT '  ⚠️  Operations may take significant time on large databases';
        PRINT '  ⚠️  Requires additional disk space for log operations';
        PRINT '  ⚠️  Full backup required after SIMPLE recovery to restore log chain';
        PRINT '';
        PRINT 'DISCLAIMER:';
        PRINT '  These are template scripts based on current metrics. You are solely';
        PRINT '  responsible for testing, customizing, and executing these scripts.';
        PRINT '  The author assumes no liability for any issues resulting from their use.';
        PRINT '  Execution is entirely at your own risk.';
        PRINT '';
        PRINT '  Always consult your organization''s change management and maintenance';
        PRINT '  procedures before making database configuration changes.';
        PRINT '*/';
        PRINT '/*******************************************************************************';
        PRINT '';
        PRINT '';

        DECLARE @ScriptDB SYSNAME;
        DECLARE @ScriptVLFCount INT;
        DECLARE @ScriptLogSizeMB DECIMAL(18,2);
        DECLARE @ScriptRecommendedSizeMB INT;
        DECLARE @ScriptRecommendedGrowthMB INT;

        DECLARE script_cursor CURSOR LOCAL FAST_FORWARD FOR
            SELECT DatabaseName, VLFCount, LogSizeMB
            FROM #LogHealth
            WHERE Priority <= 2  -- CRITICAL or WARNING
            ORDER BY Priority, DatabaseName;

        OPEN script_cursor;
        FETCH NEXT FROM script_cursor INTO @ScriptDB, @ScriptVLFCount, @ScriptLogSizeMB;

        WHILE @@FETCH_STATUS = 0
        BEGIN
            -- Calculate recommendations
            SET @ScriptRecommendedSizeMB = CAST(@ScriptLogSizeMB * 1.2 AS INT);
            SET @ScriptRecommendedGrowthMB = CASE
                WHEN @ScriptRecommendedSizeMB > 10000 THEN 1024
                WHEN @ScriptRecommendedSizeMB > 5000 THEN 512
                ELSE 256
            END;

            PRINT '/*******************************************************************************';
            PRINT 'DATABASE: ' + @ScriptDB;
            PRINT '*******************************************************************************/';
            PRINT '/*';
            PRINT 'Current Metrics:';
            PRINT '  VLF Count: ' + CAST(@ScriptVLFCount AS VARCHAR(10));
            PRINT '  Log Size: ' + CAST(@ScriptLogSizeMB AS VARCHAR(20)) + ' MB';
            PRINT '';
            PRINT 'Recommendations:';
            PRINT '  Log Size: ' + CAST(@ScriptRecommendedSizeMB AS VARCHAR(20)) + ' MB';
            PRINT '  Autogrowth: ' + CAST(@ScriptRecommendedGrowthMB AS VARCHAR(20)) + ' MB (fixed)';
            PRINT '';
            PRINT 'NOTE: These are estimates. Review and adjust based on your workload.';
            PRINT '*/';
            PRINT '';

            IF @ScriptVLFCount > 500
            BEGIN
                PRINT '-- ============================================================================';
                PRINT '-- ⚠️  STEP 1: REBUILD LOG TO REDUCE VLF FRAGMENTATION';
                PRINT '-- ⚠️  MAINTENANCE WINDOW REQUIRED - HIGH IMPACT OPERATION';
                PRINT '-- ============================================================================';
                PRINT '-- CRITICAL WARNINGS:';
                PRINT '--   • Switches to SIMPLE recovery (BREAKS LOG CHAIN)';
                PRINT '--   • DBCC SHRINKFILE causes high I/O and blocking';
                PRINT '--   • Database may be unavailable during shrink operation';
                PRINT '--   • Full backup required after completion to restore log chain';
                PRINT '--   • DO NOT run during business hours or on production without approval';
                PRINT '--';
                PRINT '-- PREREQUISITES:';
                PRINT '--   • Full database backup completed and verified';
                PRINT '--   • Approved maintenance window scheduled';
                PRINT '--   • No active transactions or long-running queries';
                PRINT '--   • Monitor disk space (may need temporary space during operation)';
                PRINT '--';
                PRINT '-- RECOMMENDED: Test in non-production first!';
                PRINT '';
                PRINT '-- Truncate active log before switching recovery model';
                PRINT 'BACKUP LOG [' + @ScriptDB + '] TO DISK = N''NUL:'';';
                PRINT 'GO';
                PRINT '';
                PRINT '-- Switch to SIMPLE recovery (breaks log chain)';
                PRINT 'ALTER DATABASE [' + @ScriptDB + '] SET RECOVERY SIMPLE;';
                PRINT 'GO';
                PRINT '';
                PRINT '-- Shrink log file (high I/O operation - may take significant time)';
                PRINT 'DBCC SHRINKFILE (N''' + @ScriptDB + '_log'', 1);';
                PRINT 'GO';
                PRINT '';
                PRINT '-- Restore FULL recovery model';
                PRINT 'ALTER DATABASE [' + @ScriptDB + '] SET RECOVERY FULL;';
                PRINT 'GO';
                PRINT '';
                PRINT '-- ⚠️  CRITICAL: Take full backup immediately to restore log chain!';
                PRINT '-- BACKUP DATABASE [' + @ScriptDB + '] TO DISK = N''<your_backup_path>\' + @ScriptDB + '_post_rebuild.bak'';';
                PRINT '-- GO';
                PRINT '';
            END

            PRINT '-- ============================================================================';
            PRINT '-- STEP ' + CASE WHEN @ScriptVLFCount > 500 THEN '2' ELSE '1' END + ': SET OPTIMAL SIZE AND AUTOGROWTH';
            PRINT '-- ============================================================================';
            PRINT '-- NOTE: This operation is lower risk but may still cause brief blocking.';
            PRINT '--';
            PRINT 'ALTER DATABASE [' + @ScriptDB + '] MODIFY FILE (';
            PRINT '    NAME = N''' + @ScriptDB + '_log'',';
            PRINT '    SIZE = ' + CAST(@ScriptRecommendedSizeMB AS VARCHAR(20)) + 'MB,';
            PRINT '    FILEGROWTH = ' + CAST(@ScriptRecommendedGrowthMB AS VARCHAR(20)) + 'MB';
            PRINT ');';
            PRINT 'GO';
            PRINT '';
            PRINT '-- ============================================================================';
            PRINT '-- STEP ' + CASE WHEN @ScriptVLFCount > 500 THEN '3' ELSE '2' END + ': VERIFY CHANGES';
            PRINT '-- ============================================================================';
            PRINT '';
            PRINT '-- Verify VLF count (should be significantly reduced if rebuild was performed)';
            PRINT 'SELECT COUNT(*) AS VLFCount FROM sys.dm_db_log_info(DB_ID(''' + @ScriptDB + '''));';
            PRINT 'GO';
            PRINT '';
            PRINT '-- Verify log file configuration';
            PRINT 'SELECT';
            PRINT '    name,';
            PRINT '    size * 8 / 1024 AS SizeMB,';
            PRINT '    growth,';
            PRINT '    is_percent_growth,';
            PRINT '    CASE WHEN is_percent_growth = 0 THEN CAST(growth * 8 / 1024 AS VARCHAR(20)) + '' MB''';
            PRINT '         ELSE CAST(growth AS VARCHAR(20)) + '' %'' END AS GrowthSetting';
            PRINT 'FROM sys.master_files';
            PRINT 'WHERE database_id = DB_ID(''' + @ScriptDB + ''') AND type = 1;';
            PRINT 'GO';
            PRINT '';
            PRINT '/*******************************************************************************';
            PRINT 'POST-EXECUTION CHECKLIST for ' + @ScriptDB + ':';
            PRINT '  ☐ Full backup completed (if recovery model was changed)';
            PRINT '  ☐ VLF count reduced to acceptable level (<200 is ideal)';
            PRINT '  ☐ Log file sized appropriately for workload';
            PRINT '  ☐ Autogrowth set to fixed MB (not percent)';
            PRINT '  ☐ Database operational and applications functioning normally';
            PRINT '  ☐ Change documented in your change management system';
            PRINT '*******************************************************************************/';
            PRINT '';
            PRINT '================================================================================';
            PRINT '';

            FETCH NEXT FROM script_cursor INTO @ScriptDB, @ScriptVLFCount, @ScriptLogSizeMB;
        END

        CLOSE script_cursor;
        DEALLOCATE script_cursor;

        PRINT '';
        PRINT '/*******************************************************************************';
        PRINT '                         END OF OPTIMIZATION SCRIPTS';
        PRINT '*******************************************************************************/';
        PRINT '';
        PRINT 'REMINDER: These scripts are templates for your review. They require:';
        PRINT '  • Careful review and customization for your environment';
        PRINT '  • Testing in non-production before production use';
        PRINT '  • Execution during approved maintenance windows';
        PRINT '  • Proper change management and documentation';
        PRINT '';
        PRINT 'You are responsible for all changes made to your databases.';
        PRINT 'Execute at your own risk.';
        PRINT '';
        PRINT '/*******************************************************************************';
        PRINT '';
    END

    -- Cleanup
    DROP TABLE #LogHealth;

    PRINT '';
    PRINT '================================================================================';
    PRINT 'Health checkup completed: ' + CONVERT(VARCHAR(30), GETDATE(), 120);
    PRINT '================================================================================';
    PRINT '';

END
GO

GRANT EXECUTE ON [dbo].[sp_TransactionLogHealthCheckup] TO public;
GO

PRINT '';
PRINT '================================================================================';
PRINT '   sp_TransactionLogHealthCheckup created successfully!';
PRINT '================================================================================';
PRINT '';
PRINT 'Usage Examples:';
PRINT '';
PRINT '  -- Quick checkup (all databases):';
PRINT '  EXEC [master].[dbo].[sp_TransactionLogHealthCheckup];';
PRINT '';
PRINT '  -- Checkup for specific database:';
PRINT '  EXEC [master].[dbo].[sp_TransactionLogHealthCheckup] @DatabaseName = ''YourDB'';';
PRINT '';
PRINT '  -- Show only databases with issues:';
PRINT '  EXEC [master].[dbo].[sp_TransactionLogHealthCheckup] @ShowHealthyDatabases = 0;';
PRINT '';
PRINT '  -- Generate optimization scripts:';
PRINT '  EXEC [master].[dbo].[sp_TransactionLogHealthCheckup] @GenerateScripts = 1;';
PRINT '';
PRINT '================================================================================';
PRINT '';
GO