SQL SERVER: Archive table data stored procedure

sp_ArchiveTableData.sql

/* EXEC sp_helptext 'dbo.sp_ArchiveTableData' */
USE [master]
GO


/*
    Author: Tim Cartwright
    
    Purpose: 
        Archives large tables using a "nibbling delete" pattern - moving data in small batches 
        to prevent lock escalation and excessive transaction log growth.
    
    Key Features:
        - Automatically calculates optimal batch size to avoid lock escalation (default: ~80% of 5000 lock threshold)
        - Optional adaptive statistics updates when batch performance degrades
        - Optional adaptive index maintenance when fragmentation exceeds 40%
        - Progress tracking with ETA calculations
        - Creates archive database and table structure automatically
    
    Best Practices:
        BEFORE RUNNING:
        - Take a full backup (or use @BackupSourceDB = 1)
        - Drop non-clustered indexes on source table for faster deletes (Can skip)
        - Ensure adequate disk space for archive database and transaction logs
        
        AFTER RUNNING:
        - Re-create non-clustered indexes on source table (If dropped)
        - Take full backup to reset log sequence and allow log truncation
        - Verify row counts match between source and archive (if @DropArchiveTable = 0)
    
    Performance Notes:
        - @NibblingDeletesBatchSize: Leave at 0 (default) to auto-calculate safe batch size
          Set higher value only if lock escalation is not a concern (speeds up process but risks log growth)
        
        - @EnableAdaptiveStats: Disabled by default. Enable only if significant performance degradation occurs
          Can slow down process but may help maintain consistent batch times
        
        - @EnableAdaptiveIndexMaint: Disabled by default and NOT recommended during archiving
          Can be extremely slow and causes schema locks. Better to handle index maintenance separately
    
    Important:
        - Source table MUST have a primary key (heap tables not supported)
        - If archiving to same database, MUST use different schema names
        - Archive table name will always match source table name

    Workflow:
        1. VALIDATION & INITIALIZATION
           - Validates all required parameters (@SourceDB, @TableName)
           - Normalizes parameter values (removes brackets, sets defaults)
           - Validates recovery model options
           - Validates adaptive stats configuration (threshold vs interval)
           - Ensures source and archive aren't same DB+schema combination
           
        2. BATCH SIZE CALCULATION
           - If @NibblingDeletesBatchSize = 0 (default):
             * Queries sys.dm_db_partition_stats to get actual rows per page
             * Calculates optimal batch size = rows_per_page × 4000 (80% of 5000 lock threshold)
             * Enforces minimum batch size of 1000 rows
           - This prevents lock escalation while maximizing throughput, and also prevents excessive transaction log growth.
           
        3. ROW COUNT ASSESSMENT
           - Executes dynamic COUNT(*) with @AdditionalSQL filter
           - Calculates expected number of batches (@PossibleLoopCount)
           - Exits early if no rows match criteria
           
        4. PARAMETER LOGGING
           - Outputs all parameters with formatted values for audit trail
           - Displays calculated batch size and expected loop count
           
        5. OPTIONAL BACKUP
           - If @BackupSourceDB = 1:
             * Checks msdb.dbo.backupset for existing copy-only backup in last 24 hours
             * Creates new copy-only backup only if none exists
             * Uses compression and ISO 8601 timestamp in filename
           
        6. PRIMARY KEY EXTRACTION
           - Queries sys.indexes, sys.index_columns, sys.columns
           - Extracts PK name, column list with ASC/DESC, and clustered flag
           - Builds comparison clause for joins (@PKColumnsCompare)
           - Throws error if no PK exists (heap tables not supported)
           
        7. ARCHIVE DATABASE CREATION
           - Creates database if it doesn't exist
           - Uses specified @DataPath and @LogPath
           - Sets initial sizes: 1GB data file, 512MB log file
           - Growth increments: 256MB for both files
           
        8. ARCHIVE SCHEMA CREATION
           - Creates schema in archive database if @ArchiveSchemaName != 'dbo'
           - Skips if schema already exists
           
        9. RECOVERY MODEL CONFIGURATION
           - Sets source database to SIMPLE recovery for minimal logging
           - Sets archive database to SIMPLE (if different from source)
           
        10. COLUMN LIST GENERATION
            - Queries sys.columns ordered by column_id
            - Builds comma-separated list with [t]. prefix for DELETE clause
            - Formats with line breaks every 10 columns for readability
            
        11. ARCHIVE TABLE STRUCTURE
            - Creates empty table using SELECT...INTO...WHERE 1=0 pattern
            - Preserves all column definitions and data types
            - Skips if table already exists
            
        12. PRIMARY KEY REPLICATION
            - If @CreateArchivePK = 1 and @DropArchiveTable = 0:
              * Creates matching PK on archive table with "_Archive" suffix
              * Preserves CLUSTERED/NONCLUSTERED designation
              * Preserves column order and ASC/DESC specifications
              * Skips if PK already exists
            
        13. MAIN ARCHIVING LOOP
            - Enables IDENTITY_INSERT if table has identity column
            - Loops while @RowsAffected = @BatchSize:
              
              a. DELETE TOP (@BatchSize) with OUTPUT INTO
                 - Deletes batch of rows from source
                 - Simultaneously inserts deleted rows into archive
                 - All within single transaction (ACID compliant)
              
              b. Progress Tracking (every batch)
                 - Calculates elapsed time for current batch
                 - Maintains rolling window of last 50 batch times
                 - Computes median batch time from last 20 batches
                 - Calculates percent complete, remaining rows, and ETA
                 - Outputs formatted progress message with timestamp
              
              c. Performance Optimization
                 - Executes CHECKPOINT every 10 batches
                 - 500ms delay between batches for log truncation
              
              d. Adaptive Statistics (if @EnableAdaptiveStats = 1)
                 - Interval-based: Updates stats every @AdaptiveStatsBatchInterval batches
                 - Slowdown-based: Detects when batch time > 2× median
                   * Increments slowdown counter
                   * Updates stats after @AdaptiveStatsSlowdownThreshold consecutive slow batches
                   * Requires 15+ minutes since last update
                 - Uses 10% sampling for speed
              
              e. Adaptive Index Maintenance (if @EnableAdaptiveIndexMaint = 1)
                 - Only runs after batch 5 (needs baseline)
                 - Checks every 50 batches if 60+ minutes elapsed
                 - Queries sys.dm_db_index_physical_stats with LIMITED scan
                 - If fragmentation >= 40%:
                   * Reorganizes all non-heap indexes using cursor
                   * Updates statistics with 10% sample
                   * Resets maintenance timers
                 - WARNING: Can cause severe slowdowns and schema locks
            
            - Disables IDENTITY_INSERT after loop completes
            
        14. FINAL STATISTICS UPDATE
            - Updates statistics on source table
            - Lets SQL Server choose optimal sampling percentage
            - Ensures accurate cardinality estimates after mass deletion
            
        15. OPTIONAL CLEANUP
            - If @DropArchiveTable = 1:
              * Drops archive table (permanent deletion scenario)
              * Leaves archive database intact for future use
            
        16. RECOVERY MODEL RESTORATION
            - Restores source database to @SourceDBRecoveryModel
            - Runs in both success and error paths (critical for log management)
            
        17. ERROR HANDLING
            - Catches all errors via TRY/CATCH
            - Rolls back any open transactions
            - Ensures recovery model is restored even on failure
            - Re-throws original error with full context
*/
CREATE OR ALTER PROC [dbo].[sp_ArchiveTableData] (
    /* Source database name */
    @SourceDB SYSNAME,
    
    /* Source table schema (default: dbo) */
    @SourceSchemaName SYSNAME = 'dbo',
    
    /* Archive database name (default: [SourceDB]_Archive) */
    @ArchiveDB SYSNAME = NULL, 
    
    /* Archive table schema (default: dbo) */
    @ArchiveSchemaName SYSNAME = 'dbo',
    
    /* Table name to archive (same name used in archive database) */
    @TableName SYSNAME,
    
    /* WHERE clause or JOIN conditions for filtering the source tables rows to archive
       Example: 'WHERE CreatedDate < ''2023-01-01'''
       If NULL or empty, entire table is archived */
    @AdditionalSQL NVARCHAR(MAX) = NULL,
    
    /* Create copy-only backup before archiving (only if none exists in last 24 hours) */
    @BackupSourceDB BIT = 0,
    
    /* Create primary key on archive table (ignored if @DropArchiveTable = 1) */
    @CreateArchivePK BIT = 1,
    
    /* Drop archive table after completion (useful for permanent deletion vs preservation) */
    @DropArchiveTable BIT = 0,
    
    /* File path for archive database data files (if database needs to be created) */
    @DataPath NVARCHAR(260) = 'F:\Data\',
    
    /* File path for archive database log files (if database needs to be created) */
    @LogPath NVARCHAR(260) = 'F:\Data\',
    
    /* Rows per batch (0 = auto-calculate to prevent lock escalation, recommended)
       Set higher value only if you need speed over safety. A minimum of 1000 is enforced. */
    @NibblingDeletesBatchSize INT = 0,
    
    /* Recovery model to restore on source database after completion */
    @SourceDBRecoveryModel VARCHAR(20) = 'FULL',
    
    /* Enable automatic statistics updates if batch performance degrades
       Disabled by default - can slow down process significantly */
    @EnableAdaptiveStats BIT = 0,  
    
    /* Number of consecutive slow batches before triggering stats update
       Only applies when @EnableAdaptiveStats = 1, Use 0 to disable slowdown check. 
       If @EnableAdaptiveStats = 1 then either @AdaptiveStatsSlowdownThreshold or @AdaptiveStatsBatchInterval must be enabled. Both cannot be enabled at the same time. */
    @AdaptiveStatsSlowdownThreshold INT = 0,

    /* Number of consecutive batches before triggering stats update
       Only applies when @EnableAdaptiveStats = 1, Use 0 to disable interval check.
       If @EnableAdaptiveStats = 1 then either @AdaptiveStatsSlowdownThreshold or @AdaptiveStatsBatchInterval must be enabled. Both cannot be enabled at the same time. Must also be greater than 5. */
    @AdaptiveStatsBatchInterval INT = 0,
    
    /* Enable periodic index reorganization during archiving
       NOT RECOMMENDED - can cause severe slowdowns and schema locks */
    @EnableAdaptiveIndexMaint BIT = 0, 
    
    /* Print all dynamic SQL statements for troubleshooting */
    @Debug BIT = 0
) AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    DECLARE @ErrorMessage VARCHAR(MAX) = ''
    DECLARE @crlf CHAR(2) = CHAR(13) + CHAR(10)

    /* Reset parameters to defaults if passed as NULL */
    SELECT @SourceSchemaName = ISNULL(@SourceSchemaName, 'dbo')
        ,@ArchiveSchemaName = ISNULL(@ArchiveSchemaName, 'dbo')
        ,@BackupSourceDB = ISNULL(@BackupSourceDB, 0)
        ,@CreateArchivePK = ISNULL(@CreateArchivePK, 1)
        ,@DropArchiveTable = ISNULL(@DropArchiveTable, 0)
        /* DO NOT RESET THESE. */
        --    ,@DataPath = ISNULL(@DataPath, 'F:\Data\')
        --    ,@LogPath = ISNULL(@LogPath, 'F:\Data\')
        ,@NibblingDeletesBatchSize = ISNULL(@NibblingDeletesBatchSize, 0)
        ,@SourceDBRecoveryModel = ISNULL(@SourceDBRecoveryModel, 'FULL')
        ,@EnableAdaptiveStats = ISNULL(@EnableAdaptiveStats, 0)
        ,@AdaptiveStatsSlowdownThreshold = ISNULL(@AdaptiveStatsSlowdownThreshold, 0)
        ,@AdaptiveStatsBatchInterval = ISNULL(@AdaptiveStatsBatchInterval, 0)
        ,@EnableAdaptiveIndexMaint = ISNULL(@EnableAdaptiveIndexMaint, 0)
        ,@Debug = ISNULL(@Debug, 0)

    /* clean object names of all quoted identifiers in case they send them in. hopefully they dont use double quotes... */
    SELECT @SourceDB = REPLACE(REPLACE(@SourceDB, '[', ''), ']', '')
        ,@ArchiveDB = REPLACE(REPLACE(@ArchiveDB, '[', ''), ']', '')
        ,@SourceSchemaName = REPLACE(REPLACE(@SourceSchemaName, '[', ''), ']', '')
        ,@ArchiveSchemaName = REPLACE(REPLACE(@ArchiveSchemaName, '[', ''), ']', '')
        ,@TableName = REPLACE(REPLACE(@TableName, '[', ''), ']', '')
    /*  ensure the paths end with back slash */
    IF @DataPath IS NOT NULL AND RIGHT(@DataPath, 1) <> '\' BEGIN SET @DataPath += '\' END
    IF @LogPath IS NOT NULL AND RIGHT(@LogPath, 1) <> '\' BEGIN SET @LogPath += '\' END

    /* the database to archive data to */
    SELECT @ArchiveDB = COALESCE(@ArchiveDB, CONCAT(@SourceDB, '_Archive'))
        , @AdditionalSQL = TRIM(@AdditionalSQL)
    
    DECLARE 
         @SQL NVARCHAR(MAX)
        ,@RowCount INT
        ,@TotalRows BIGINT
        ,@Msg NVARCHAR(500)
        ,@BitToTinyInt TINYINT = 0
        ,@NibblingDeletesBatchSizeTemp INT
        ,@PrintDate VARCHAR(30)
        ,@PossibleLoopCount INT
        ,@DateFormat VARCHAR(30) = 'yyyy-MM-dd HH:mm:ss.fff'
        ,@PrintableNum VARCHAR(30) = ''
    
    DECLARE @QuotedSourceDB NVARCHAR(128) = QUOTENAME(@SourceDB)
    DECLARE         
         @IsClustered BIT    
        ,@PKName sysname = ''
        ,@PKColumns VARCHAR(8000) = ''
        ,@PKColumnsCompare VARCHAR(8000) = ''
        ,@ColumnNames VARCHAR(MAX) = ''
        ,@FullSourceTableName VARCHAR(1000) = CONCAT(@QuotedSourceDB, '.', QUOTENAME(@SourceSchemaName), '.', QUOTENAME(@TableName))
        ,@FullArchiveTableName VARCHAR(1000) = CONCAT(QUOTENAME(@ArchiveDB), '.', QUOTENAME(@ArchiveSchemaName), '.', QUOTENAME(@TableName))


    IF @ArchiveDB = @SourceDB AND @ArchiveSchemaName = @SourceSchemaName BEGIN
        SET @ErrorMessage += ' - The @SourceDB and the @ArchiveDB cannot be the same database unless the @ArchiveSchemaName and @SourceSchemaName are different.' + @crlf
    END
    IF LEN(@SourceDB) = 0 OR @SourceDB IS NULL BEGIN
        SET @ErrorMessage += ' - Parameter @SourceDB is required and cannot be null or empty.' + @crlf
    END
    IF LEN(@TableName) = 0 OR @TableName IS NULL BEGIN
        SET @ErrorMessage += ' - Parameter @TableName is required and cannot be null or empty.' + @crlf
    END
    IF OBJECT_ID(@FullSourceTableName) IS NULL BEGIN
        SET @ErrorMessage += ' - The source table ''' + @FullSourceTableName + ''' could not be found.' + @crlf
    END
    IF @EnableAdaptiveStats = 1 BEGIN
        IF @AdaptiveStatsSlowdownThreshold <= 0 AND @AdaptiveStatsBatchInterval <= 0 BEGIN
            SET @ErrorMessage += ' - When @EnableAdaptiveStats = 1 then either @AdaptiveStatsSlowdownThreshold or @AdaptiveStatsBatchInterval must be greated than zero.' + @crlf
        END ELSE IF @AdaptiveStatsSlowdownThreshold > 0 AND @AdaptiveStatsBatchInterval > 0 BEGIN
            SET @ErrorMessage += ' - When @EnableAdaptiveStats = 1 then should only enable @AdaptiveStatsSlowdownThreshold or @AdaptiveStatsBatchInterval. You should not enable both.' + @crlf
        END
        
        IF @AdaptiveStatsBatchInterval > 0 AND @AdaptiveStatsBatchInterval < 5 BEGIN
            SET @ErrorMessage += ' - @AdaptiveStatsBatchInterval should be greater than or equal to 5 if set.' + @crlf
        END
    END
    IF @SourceDBRecoveryModel NOT IN ('FULL', 'BULK_LOGGED', 'SIMPLE') BEGIN
        SET @ErrorMessage += ' - @SourceDBRecoveryModel is invalid. It must be one of these values: FULL | BULK_LOGGED | SIMPLE.' + @crlf
    END

    IF LEN(@ErrorMessage) > 0 BEGIN
        ;THROW 50000, @ErrorMessage, 1;
        RETURN -1;
    END

    BEGIN TRY
        /**************************************************************************************/
        /**************************************************************************************/
        /**************************************************************************************/
        /**************************************************************************************/
        SET @PrintDate = FORMAT(GETDATE(), @DateFormat)
        RAISERROR('[%s] Archive Process Starting.', 0, 1, @PrintDate) WITH NOWAIT;

        /**************************************************************************************/
        /**************************************************************************************/
        IF ISNULL(@NibblingDeletesBatchSize, 0) <= 0 BEGIN
            SET @PrintDate = FORMAT(GETDATE(), @DateFormat)
            RAISERROR('[%s] Getting the optimal batch size for the nibbling deletes to avoid lock escalation.', 0, 1, @PrintDate) WITH NOWAIT;

            /* If they don't send in a delete batch size, lets calculate it based upon rows per page */
            /* We want to avoid lock escalation, and log file growing out of control */
            SET @SQL = CONCAT('
                /* Calculate optimal batch size based on actual rows per page */
                DECLARE @RowsPerPage INT
                DECLARE @MaxLocks INT = 5000 * 0.80  /* % of 5000 threshold for lock escalation. leave wiggle room for jagged rows */

                /* Get rows per page */
                SELECT @RowsPerPage = 
                    CASE 
                        WHEN SUM(used_page_count) > 0 
                            THEN SUM(row_count) / SUM(used_page_count)
                        ELSE 100  /* Default assumption */
                    END
                FROM ', @QuotedSourceDB, '.sys.dm_db_partition_stats
                WHERE object_id = OBJECT_ID(''', @FullSourceTableName,''')
                    AND index_id IN (0, 1)

                /* Calculate optimal batch size */
                SET @OptimalBatchSize = @RowsPerPage * @MaxLocks
            ')

            IF @Debug = 1 BEGIN RAISERROR('%s', 0, 1, @SQL) WITH NOWAIT; END
            EXEC sys.sp_executesql @stmt = @SQL, 
                @params = N'@OptimalBatchSize INT OUTPUT', 
                @OptimalBatchSize = @NibblingDeletesBatchSizeTemp OUTPUT;

            SET @NibblingDeletesBatchSize = @NibblingDeletesBatchSizeTemp
        END
        
        IF @NibblingDeletesBatchSize < 1000 BEGIN
            SET @PrintDate = FORMAT(GETDATE(), @DateFormat)
            RAISERROR('[%s] The @NibblingDeletesBatchSize is less than the minimum. Setting @NibblingDeletesBatchSize to the minimum value.', 0, 1, @PrintDate) WITH NOWAIT;

            SET @NibblingDeletesBatchSize = 1000
        END

        SET @PrintDate = FORMAT(GETDATE(), @DateFormat)
        RAISERROR('[%s] Getting the total row count matching our criteria to archive.', 0, 1, @PrintDate) WITH NOWAIT;
        SET @SQL = CONCAT('
            SELECT @TotalRows = COUNT(*)
            FROM ', @FullSourceTableName, ' AS [t]
            ', @AdditionalSQL)

        IF @Debug = 1 BEGIN RAISERROR('%s', 0, 1, @SQL) WITH NOWAIT; END
        EXEC sys.sp_executesql @stmt = @SQL, 
            @params = N'@TotalRows BIGINT OUTPUT', 
            @TotalRows = @TotalRows OUTPUT;
        
        IF @TotalRows = 0 BEGIN
            SET @PrintDate = FORMAT(GETDATE(), @DateFormat) 
            RAISERROR('[%s] Could not find any rows matching our criteria. Exiting...', 0, 1, @PrintDate) WITH NOWAIT;
            RETURN 0;
        END

        IF @TotalRows > @NibblingDeletesBatchSize BEGIN
            SET @PossibleLoopCount = @TotalRows / @NibblingDeletesBatchSize
            /* if there are any extra rows, we need to increase the loop size by one */
            IF @TotalRows % @NibblingDeletesBatchSize > 0 BEGIN
                SET @PossibleLoopCount += 1
            END
        END ELSE BEGIN
            SET @PossibleLoopCount = 1
        END

        /**************************************************************************************/
        /**************************************************************************************/
        /**************************************************************************************/
        /**************************************************************************************/
        SET @PrintDate = FORMAT(GETDATE(), @DateFormat) 
        DECLARE @UserName VARCHAR(512) = ORIGINAL_LOGIN()
         
        RAISERROR('[%s] %-35s: %s', 0, 1, @PrintDate, '@@SERVERNAME', @@SERVERNAME) WITH NOWAIT;
        RAISERROR('[%s] %-35s: %s', 0, 1, @PrintDate, 'Run By', @UserName) WITH NOWAIT;
        RAISERROR('[%s] %-35s: %s', 0, 1, @PrintDate, '@SourceDB', @SourceDB) WITH NOWAIT;
        RAISERROR('[%s] %-35s: %s', 0, 1, @PrintDate, '@SourceSchemaName', @SourceSchemaName) WITH NOWAIT;
        RAISERROR('[%s] %-35s: %s', 0, 1, @PrintDate, '@ArchiveDB', @ArchiveDB) WITH NOWAIT;
        RAISERROR('[%s] %-35s: %s', 0, 1, @PrintDate, '@ArchiveSchemaName', @ArchiveSchemaName) WITH NOWAIT;
        RAISERROR('[%s] %-35s: %s', 0, 1, @PrintDate, '@TableName', @TableName) WITH NOWAIT;
        RAISERROR('[%s] %-35s: %s', 0, 1, @PrintDate, 'Archiving from', @FullSourceTableName) WITH NOWAIT;
        RAISERROR('[%s] %-35s: %s', 0, 1, @PrintDate, 'Archiving to', @FullArchiveTableName) WITH NOWAIT;
        RAISERROR('[%s] %-35s: %s', 0, 1, @PrintDate, '@AdditionalSQL', @AdditionalSQL) WITH NOWAIT;
        SET @BitToTinyInt = @BackupSourceDB
        RAISERROR('[%s] %-35s: %d', 0, 1, @PrintDate, '@BackupSourceDB', @BitToTinyInt) WITH NOWAIT;
        SET @BitToTinyInt = @CreateArchivePK
        RAISERROR('[%s] %-35s: %d', 0, 1, @PrintDate, '@CreateArchivePK', @BitToTinyInt) WITH NOWAIT;
        SET @BitToTinyInt = @DropArchiveTable
        RAISERROR('[%s] %-35s: %d', 0, 1, @PrintDate, '@DropArchiveTable', @BitToTinyInt) WITH NOWAIT;
        RAISERROR('[%s] %-35s: %s', 0, 1, @PrintDate, '@DataPath', @DataPath) WITH NOWAIT;
        RAISERROR('[%s] %-35s: %s', 0, 1, @PrintDate, '@LogPath', @LogPath) WITH NOWAIT;
        SET @PrintableNum = FORMAT(@NibblingDeletesBatchSize, 'N0')
        RAISERROR('[%s] %-35s: %s', 0, 1, @PrintDate, '@NibblingDeletesBatchSize', @PrintableNum) WITH NOWAIT;
        RAISERROR('[%s] %-35s: %s', 0, 1, @PrintDate, '@SourceDBRecoveryModel', @SourceDBRecoveryModel) WITH NOWAIT;
        SET @BitToTinyInt = @EnableAdaptiveStats
        RAISERROR('[%s] %-35s: %d', 0, 1, @PrintDate, '@EnableAdaptiveStats', @BitToTinyInt) WITH NOWAIT;
        RAISERROR('[%s] %-35s: %d', 0, 1, @PrintDate, '@AdaptiveStatsSlowdownThreshold', @AdaptiveStatsSlowdownThreshold) WITH NOWAIT;
        RAISERROR('[%s] %-35s: %d', 0, 1, @PrintDate, '@AdaptiveStatsBatchInterval', @AdaptiveStatsBatchInterval) WITH NOWAIT;
        SET @BitToTinyInt = @EnableAdaptiveIndexMaint
        RAISERROR('[%s] %-35s: %d', 0, 1, @PrintDate, '@EnableAdaptiveIndexMaint', @BitToTinyInt) WITH NOWAIT;
        SET @BitToTinyInt = @Debug
        RAISERROR('[%s] %-35s: %d', 0, 1, @PrintDate, '@Debug', @BitToTinyInt) WITH NOWAIT;
        SET @PrintableNum = FORMAT(@TotalRows, 'N0')
        RAISERROR('[%s] %-35s: %s', 0, 1, @PrintDate, '@TotalRows', @PrintableNum) WITH NOWAIT;
        SET @PrintableNum = FORMAT(@PossibleLoopCount, 'N0')
        RAISERROR('[%s] %-35s: %s', 0, 1, @PrintDate, '@PossibleLoopCount', @PrintableNum) WITH NOWAIT;
        
        /**************************************************************************************/
        /**************************************************************************************/
        IF @BackupSourceDB = 1 BEGIN
            SET @PrintDate = FORMAT(GETDATE(), @DateFormat)
            RAISERROR('[%s] Backing up source db if copy only does not already exist...', 0, 1, @PrintDate) WITH NOWAIT;

            DECLARE @DateTime NVARCHAR(30);
            DECLARE @LastCopyOnlyBackup DATETIME;

            /* Check for copy-only backup within last 24 hours */
            SELECT TOP (1) 
                @LastCopyOnlyBackup = backup_finish_date
            FROM msdb.dbo.backupset
            WHERE database_name = @SourceDB
                AND type = 'D' /* Full backup */
                AND is_copy_only = 1
            ORDER BY backup_finish_date DESC;

            IF @LastCopyOnlyBackup IS NULL OR @LastCopyOnlyBackup < DATEADD(DAY, -1, GETDATE())
            BEGIN
                SET @DateTime = REPLACE(CONVERT(NVARCHAR(19), GETDATE(), 126), ':', '-'); /* ISO 8601 format */
                SET @PrintDate = FORMAT(GETDATE(), @DateFormat)
                RAISERROR('[%s] Backing up source db to full copy only backup.', 0, 1, @PrintDate) WITH NOWAIT;
                SET @SQL = CONCAT('
                    BACKUP DATABASE [', @SourceDB, ']
                    TO DISK = N''', @SourceDB, '_full_copyonly_', @DateTime, '.bak''
                    WITH NOFORMAT, INIT, COPY_ONLY, NAME = N''', @SourceDB, '_Full_Copy_Only_Backup'', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10;
                ')
                IF @Debug = 1 BEGIN RAISERROR('%s', 0, 1, @SQL) WITH NOWAIT; END
                EXEC sys.sp_executesql @stmt = @SQL
            END
            ELSE
            BEGIN
                SET @PrintDate = FORMAT(GETDATE(), @DateFormat)
                SET @Msg = CONCAT('[', @PrintDate, '] Skipping backup - copy-only backup already exists within last 24 hours, Last copy-only backup: ', CONVERT(VARCHAR(30), @LastCopyOnlyBackup, 120));
                RAISERROR(@Msg, 0, 1) WITH NOWAIT;
            END
        END

        /**************************************************************************************/
        /**************************************************************************************/
        SET @PrintDate = FORMAT(GETDATE(), @DateFormat)
        RAISERROR('[%s] Getting the primary key columns from the source table.', 0, 1, @PrintDate) WITH NOWAIT;
        /** GET THE PRIMARY KEY COLUMN NAMES IN ORDER, AND IF CLUSTERED. SO WE CAN RECREATE THEM ON THE ARCHIVE TABLE ****/
        /** THIS TECHNICALLY SLOWS DOWN THE INSERT, WE DO THIS FOR THE JOIN CLAUSE WHERE WE MOVE THE DATA BACK **/
        SET @SQL = CONCAT('
        SELECT 
            @PKName = i.name,
            @PKColumns = STRING_AGG(CONCAT(QUOTENAME(c.name), CASE WHEN ic.is_descending_key = 0 THEN '' ASC'' ELSE '' DESC'' END), '', '') WITHIN GROUP (ORDER BY ic.key_ordinal),
            @PKColumnsCompare = STRING_AGG(CONCAT(''[st].'', QUOTENAME(c.name), '' = [t].'', QUOTENAME(c.name)), ''AND '') WITHIN GROUP (ORDER BY ic.key_ordinal),
            @IsClustered = CASE WHEN i.type_desc = ''CLUSTERED'' THEN 1 ELSE 0 END
        FROM ', @QuotedSourceDB, '.sys.indexes i
        INNER JOIN ', @QuotedSourceDB, '.sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
        INNER JOIN ', @QuotedSourceDB, '.sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
        INNER JOIN ', @QuotedSourceDB, '.sys.tables t ON i.object_id = t.object_id
        INNER JOIN ', @QuotedSourceDB, '.sys.schemas s ON t.schema_id = s.schema_id
        WHERE i.is_primary_key = 1
            AND t.name = @TableName
            AND s.name = @SchemaName
        GROUP BY i.name, i.type_desc');

        IF @Debug = 1 BEGIN RAISERROR('%s', 0, 1, @SQL) WITH NOWAIT; END
        EXEC sys.sp_executesql @stmt = @SQL, 
            @params = N'@TableName NVARCHAR(128), @SchemaName NVARCHAR(128), @PKName NVARCHAR(128) OUTPUT, @PKColumns NVARCHAR(MAX) OUTPUT, @PKColumnsCompare NVARCHAR(MAX) OUTPUT, @IsClustered BIT OUTPUT',
            @TableName = @TableName, @SchemaName = @SourceSchemaName, 
            @PKName = @PKName OUTPUT, @PKColumns = @PKColumns OUTPUT, 
            @PKColumnsCompare = @PKColumnsCompare OUTPUT, @IsClustered = @IsClustered OUTPUT;

        IF @PKName IS NULL BEGIN
            ;THROW 50001, 'No primary key found on source table. This cannot be used to archive from heap tables.', 1;
            RETURN -1;
        END

        IF @Debug = 1 BEGIN
            RAISERROR('%-30s: %s', 0, 1, '@SchemaName', @SourceSchemaName) WITH NOWAIT;
            RAISERROR('%-30s: %s', 0, 1, '@TableName', @TableName) WITH NOWAIT;
            RAISERROR('%-30s: %s', 0, 1, '@PKName', @PKName) WITH NOWAIT;
            RAISERROR('%-30s: %s', 0, 1, '@PKColumns', @PKColumns) WITH NOWAIT;
            RAISERROR('%-30s: %s', 0, 1, '@PKColumnsCompare', @PKColumnsCompare) WITH NOWAIT;
            SET @BitToTinyInt = @IsClustered
            RAISERROR('%-30s: %d', 0, 1, '@IsClustered', @BitToTinyInt) WITH NOWAIT;
        END

        /* THE ALTER DBs MUST HAPPEN OUTSIDE OF THE TRANSACTION */
        /* ALSO CREATE THE ARCHIVE TABLE OUTSIDE OF THE TRANSACTION */

        /**************************************************************************************/
        /**************************************************************************************/
        SET @PrintDate = FORMAT(GETDATE(), @DateFormat)
        RAISERROR('[%s] Creating archive db %s if not exists.', 0, 1, @PrintDate, @ArchiveDB) WITH NOWAIT;
        IF NOT EXISTS (SELECT 1 FROM sys.databases WHERE name = @ArchiveDB)
        BEGIN
            SET @SQL = CONCAT('
                CREATE DATABASE [', @ArchiveDB, ']
                ON PRIMARY (
                    NAME = N''', @ArchiveDB, '_Data'',
                    FILENAME = N''', @DataPath, @ArchiveDB, '_Data.mdf'',
                    SIZE = 1024MB,
                    FILEGROWTH = 256MB
                )
                LOG ON (
                    NAME = N''', @ArchiveDB, '_Log'',
                    FILENAME = N''', @LogPath, @ArchiveDB, '_Log.ldf'',
                    SIZE = 512MB,
                    FILEGROWTH = 256MB
                );
            ');
            IF @Debug = 1 BEGIN RAISERROR('%s', 0, 1, @SQL) WITH NOWAIT; END
            EXEC sys.sp_executesql @stmt = @SQL
            SET @PrintDate = FORMAT(GETDATE(), @DateFormat)
            RAISERROR('[%s] Archive database created successfully.', 0, 1, @PrintDate) WITH NOWAIT;
        END
        ELSE
        BEGIN
            SET @PrintDate = FORMAT(GETDATE(), @DateFormat)
            RAISERROR('[%s] Archive database already exists: %s.', 0, 1, @PrintDate, @ArchiveDB) WITH NOWAIT;
        END
        
        /**************************************************************************************/
        /**************************************************************************************/
        IF @ArchiveSchemaName <> 'dbo' BEGIN
            SET @PrintDate = FORMAT(GETDATE(), @DateFormat)
            RAISERROR('[%s] Creating schema if not exists:  %s::%s.', 0, 1, @PrintDate, @ArchiveDB, @ArchiveSchemaName) WITH NOWAIT;
            SET @SQL = CONCAT('
                USE [', @ArchiveDB, ']
                IF SCHEMA_ID(''', @ArchiveSchemaName, ''') IS NULL BEGIN
                    EXEC (''CREATE SCHEMA [', @ArchiveSchemaName, '] AUTHORIZATION dbo;'') 
                END
                ELSE
                BEGIN
                    DECLARE @PrintDate VARCHAR(30) = FORMAT(GETDATE(), ''', @DateFormat, ''')
                    RAISERROR(''[%s] Schema already exists or is the dbo schema. Skipping...'', 0, 1, @PrintDate) WITH NOWAIT;
                END
            ');
            IF @Debug = 1 BEGIN RAISERROR('%s', 0, 1, @SQL) WITH NOWAIT; END
            EXEC sys.sp_executesql @stmt = @SQL
        END 

        /**************************************************************************************/
        /**************************************************************************************/
        SET @PrintDate = FORMAT(GETDATE(), @DateFormat)
        RAISERROR('[%s] Setting recovery model on DB [%s] to SIMPLE for performance.', 0, 1, @PrintDate, @SourceDB) WITH NOWAIT;
        SET @SQL = CONCAT('ALTER DATABASE [', @SourceDB, '] SET RECOVERY SIMPLE')
        IF @Debug = 1 BEGIN RAISERROR('%s', 0, 1, @SQL) WITH NOWAIT; END
        EXEC sys.sp_executesql @stmt = @SQL

        /**************************************************************************************/
        /**************************************************************************************/
        /* only set the archive db recovery model if the dbs are different */
        IF @SourceDB <> @ArchiveDB BEGIN
            SET @PrintDate = FORMAT(GETDATE(), @DateFormat)
            RAISERROR('[%s] Setting recovery model on DB [%s] to SIMPLE for performance.', 0, 1, @PrintDate, @ArchiveDB) WITH NOWAIT;
            SET @SQL = CONCAT('ALTER DATABASE [', @ArchiveDB, '] SET RECOVERY SIMPLE')
            IF @Debug = 1 BEGIN RAISERROR('%s', 0, 1, @SQL) WITH NOWAIT; END
            EXEC sys.sp_executesql @stmt = @SQL
        END

        /**************************************************************************************/
        /**************************************************************************************/
        SET @PrintDate = FORMAT(GETDATE(), @DateFormat)
        RAISERROR('[%s] Get the column names from the source table.', 0, 1, @PrintDate) WITH NOWAIT;

        /** GET THE COLUMN NAMES FOR THE SOURCE TABLE IN PROPER ORDER **/
        SET @SQL = N'
        SELECT @ColumnNames = STUFF((
            SELECT CONCAT('', [t].'', QUOTENAME([c].[name]), 
                CASE WHEN ROW_NUMBER() OVER (ORDER BY [c].[column_id]) % 10 = 0 
                     THEN CONCAT(CHAR(10), CHAR(9), CHAR(9)) 
                     ELSE '''' 
                END)
            FROM ' + @QuotedSourceDB + N'.sys.columns AS [c] 
            WHERE [c].[object_id] = OBJECT_ID(@FullSourceTableName)
            ORDER BY [c].[column_id]
            FOR XML PATH('''')
        ), 1, 2, '''')'

        IF @Debug = 1 BEGIN RAISERROR('%s', 0, 1, @SQL) WITH NOWAIT; END
        EXEC sys.sp_executesql @stmt = @SQL, 
            @params = N'@FullSourceTableName NVARCHAR(512), @ColumnNames NVARCHAR(MAX) OUTPUT', 
            @FullSourceTableName = @FullSourceTableName,
            @ColumnNames = @ColumnNames OUTPUT

        /**************************************************************************************/
        /**************************************************************************************/
        SET @PrintDate = FORMAT(GETDATE(), @DateFormat)
        RAISERROR('[%s] Creating archive table if not exists.', 0, 1, @PrintDate) WITH NOWAIT;

        SET @SQL = CONCAT('
        IF OBJECT_ID(''', @FullArchiveTableName ,''') IS NULL BEGIN
            SELECT 
                ', @ColumnNames, ' 
            INTO ', @FullArchiveTableName, '
            FROM ', @FullSourceTableName, ' AS [t]
            WHERE 1=0;
        END ELSE BEGIN
            DECLARE @PrintDate VARCHAR(30) = FORMAT(GETDATE(), ''', @DateFormat, ''')
            RAISERROR(''[%s] Archive table already exists. Skipping...'', 0, 1, @PrintDate) WITH NOWAIT;
        END
        ');
        IF @Debug = 1 BEGIN RAISERROR('%s', 0, 1, @SQL) WITH NOWAIT; END
        EXEC sys.sp_executesql @stmt = @SQL

        IF @CreateArchivePK = 1 AND @DropArchiveTable = 0 BEGIN
            SET @PrintDate = FORMAT(GETDATE(), @DateFormat)
            RAISERROR('[%s] Creating the primary key on the archive table if it does not exist.', 0, 1, @PrintDate) WITH NOWAIT;
            SET @SQL = CONCAT('
            USE [', @ArchiveDB, ']
            DECLARE @HasPrimaryKey BIT = OBJECTPROPERTY(OBJECT_ID(''', @FullArchiveTableName, '''), ''TableHasPrimaryKey'')
            DECLARE @PrintDate VARCHAR(30) =  FORMAT(GETDATE(), ''', @DateFormat, ''')

            IF @HasPrimaryKey = 0 OR @HasPrimaryKey IS NULL
            BEGIN
                ALTER TABLE ', @FullArchiveTableName, 
                        ' ADD CONSTRAINT [', @PKName, '_Archive] PRIMARY KEY ', 
                        CASE
                            WHEN @IsClustered = 1 
                                THEN 'CLUSTERED' 
                            ELSE 'NONCLUSTERED' 
                        END, 
                        ' (', @PKColumns, ')
                RAISERROR(''[%s] Primary key [', @PKName, '_Archive] copied successfully.'', 0, 1, @PrintDate) WITH NOWAIT;
            END
            ELSE
            BEGIN
                RAISERROR(''[%s] Primary key already exists on target table. Skipping...'', 0, 1, @PrintDate) WITH NOWAIT;
            END');
    
            IF @Debug = 1 BEGIN RAISERROR('%s', 0, 1, @SQL) WITH NOWAIT; END
            EXEC sys.sp_executesql @stmt = @SQL
        END

        /**************************************************************************************/
        /**************************************************************************************/
        SET @PrintDate = FORMAT(GETDATE(), @DateFormat)
        RAISERROR('[%s] Moving ALL data to the archive table that matches our criteria.', 0, 1, @PrintDate) WITH NOWAIT;
        SET @SQL = CONCAT('
        USE [', @ArchiveDB, '];
        /* we have to check for an identity, if table has one, only then can we enable identity insert */
        DECLARE @HasIdentity BIT = OBJECTPROPERTY(OBJECT_ID(''', @FullArchiveTableName, '''), ''TableHasIdentity'')
        /* init this so at least one loop runs */
        DECLARE @RowsAffected INT = @BatchSize
        DECLARE @BatchNum INT = 0
        DECLARE @Msg VARCHAR(8000) = ''''
        DECLARE @start_date DATETIME 
        DECLARE @OverallStartTime DATETIME = GETDATE()
        DECLARE @RecentBatchTimes TABLE (BatchNum INT, ElapsedSeconds INT)
        DECLARE @PrintDate VARCHAR(30) = FORMAT(GETDATE(), ''', @DateFormat, ''')
        DECLARE @LastStatsUpdate DATETIME = GETDATE()
        DECLARE @LastIndexMaint DATETIME = GETDATE()
        DECLARE @AvgRecentSeconds FLOAT = 0
        DECLARE @SlowBatchThreshold FLOAT = 0
        DECLARE @AvgRecentSecondsInt INT = 0
        DECLARE @SlowDownCount INT = 0

        DECLARE @PossibleLoopCountStr VARCHAR(20) = FORMAT(@PossibleLoopCount, ''N0'')
        DECLARE @BatchNumStr VARCHAR(20) = ''N/A''

        IF @HasIdentity = 1 BEGIN
            RAISERROR(''[%s] Enabling identity insert for %s.'', 0, 1, @PrintDate, ''', @FullArchiveTableName, ''') WITH NOWAIT;
            SET IDENTITY_INSERT [', @ArchiveSchemaName, '].[', @TableName, '] ON;
        END

        SET @PrintDate = FORMAT(GETDATE(), ''', @DateFormat, ''')
        RAISERROR(''[%s] Beginning loop of DELETE FROM %s .. OUTPUT INSERT INTO %s.'', 0, 1, @PrintDate, ''', @FullSourceTableName, ''', ''', @FullArchiveTableName, ''') WITH NOWAIT;

        WHILE @RowsAffected = @BatchSize BEGIN
            SET @start_date = GETDATE()
            SET @BatchNum += 1
            SET @BatchNumStr = FORMAT(@BatchNum, ''N0'')
            BEGIN TRAN

            DELETE TOP (@BatchSize) [t]
            OUTPUT ', REPLACE(@ColumnNames, '[t].', 'DELETED.'), '
            INTO ', @FullArchiveTableName, '(
                ', REPLACE(@ColumnNames, '[t].', ''), '
            )
            FROM ', @FullSourceTableName, ' AS [t]
            ', @AdditionalSQL, '

            SET @RowsAffected = @@ROWCOUNT
            SET @RowCount += @RowsAffected;

            DECLARE @BatchElapsed INT = DATEDIFF(SECOND, @start_date, GETDATE())
            INSERT INTO @RecentBatchTimes VALUES (@BatchNum, @BatchElapsed)

            DELETE FROM @RecentBatchTimes 
            WHERE BatchNum < @BatchNum - 50

            COMMIT TRAN

            IF @BatchNum % 10 = 0 BEGIN  /* Every 10 batches */
                CHECKPOINT;
                WAITFOR DELAY ''00:00:00.500''  
            END
            WAITFOR DELAY ''00:00:00.500''  /* Brief pause for log truncation */

            DECLARE @MedianRecentSeconds FLOAT = (
                SELECT TOP (1) PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY ElapsedSeconds) OVER()
                FROM (
                    SELECT TOP (20) ElapsedSeconds 
                    FROM @RecentBatchTimes 
                    ORDER BY BatchNum DESC
                ) recent
            )
            DECLARE @MedianRecentSecondsInt INT = CAST(@MedianRecentSeconds AS INT)

            /* output the delete status */
            IF @RowsAffected > 0 BEGIN
                SET @PrintDate = FORMAT(GETDATE(), ''', @DateFormat, ''')
                DECLARE @RemainingRows BIGINT = @TotalRows - @RowCount
                DECLARE @Elapsed VARCHAR(12) = CONVERT(VARCHAR(12), GETDATE() - @start_date, 114)
                DECLARE @Remaining VARCHAR(30) = FORMAT(@RemainingRows, ''N0'') 
                DECLARE @PctCompleteDecimal DECIMAL(5,2) = (@RowCount * 100.0) / @TotalRows 
                DECLARE @PctComplete VARCHAR(10) = CAST(@PctCompleteDecimal AS VARCHAR(10))
                DECLARE @EstFinish VARCHAR(19) = ''N/A''
                DECLARE @RowsAffectedStr VARCHAR(20) = FORMAT(@RowsAffected, ''N0'')

                IF @PctCompleteDecimal > 0.01
                BEGIN
                    DECLARE @EstRemainingSeconds FLOAT = (@RemainingRows / CAST(@RowsAffected AS FLOAT)) * @MedianRecentSeconds
                    DECLARE @EstFinishTime DATETIME = DATEADD(SECOND, @EstRemainingSeconds, GETDATE())
                    SET @EstFinish = FORMAT(@EstFinishTime, ''', @DateFormat, ''')
                END

                RAISERROR(''[%s] Batch %8s OF %s | Moved %16s | Remaining %16s | Percent Complete %6s | Elapsed %s | ETA %s'', 0, 1, @PrintDate, @BatchNumStr, @PossibleLoopCountStr, @RowsAffectedStr, @Remaining, @PctComplete, @Elapsed, @EstFinish) WITH NOWAIT;
            END

            /* only look at working on indexes if we have more than N batches */
            IF @BatchNum >= 5 BEGIN
                /* Periodic index maintenance to combat fragmentation IF parameter is enabled */
                IF @EnableAdaptiveIndexMaint = 1 AND @BatchNum % 50 = 0 AND DATEDIFF(MINUTE, @LastIndexMaint, GETDATE()) >= 60 BEGIN  
                    DECLARE @FragPercent FLOAT
                    DECLARE @FragPercentInt INT

                    SELECT @FragPercent = MAX(avg_fragmentation_in_percent)
                    FROM ', @QuotedSourceDB, '.sys.dm_db_index_physical_stats(
                        DB_ID(''', @SourceDB, '''), 
                        OBJECT_ID(''', @FullSourceTableName, '''), 
                        NULL, NULL, ''LIMITED''
                    )
                    WHERE index_id > 0 /* ignore heaps */

                    IF @FragPercent >= 40 BEGIN
                        SET @PrintDate = FORMAT(GETDATE(), ''', @DateFormat, ''')
                        SET @FragPercentInt = CAST(@FragPercent AS INT)
                        RAISERROR(''[%s] Fragmentation %d. Reorganizing...'', 0, 1, @PrintDate, @FragPercentInt) WITH NOWAIT;
        
                        DECLARE @IndexName sysname, @ReorgSQL NVARCHAR(500)
                        DECLARE idx_cur CURSOR LOCAL FAST_FORWARD FOR
                            SELECT name FROM ', @QuotedSourceDB, '.sys.indexes
                            WHERE object_id = OBJECT_ID(''', @FullSourceTableName, ''') 
                                AND index_id > 0 /* ignore heaps */
                                AND name IS NOT NULL
        
                        OPEN idx_cur
                        FETCH NEXT FROM idx_cur INTO @IndexName
                        WHILE @@FETCH_STATUS = 0 BEGIN
                            SET @ReorgSQL = N''ALTER INDEX '' + QUOTENAME(@IndexName) + N'' ON ', @FullSourceTableName, ' REORGANIZE''
                            BEGIN TRY
                                RAISERROR(''%s'', 0, 1, @ReorgSQL) WITH NOWAIT; 
                                EXEC sp_executesql @ReorgSQL
                            END TRY
                            BEGIN CATCH END CATCH
                            FETCH NEXT FROM idx_cur INTO @IndexName
                        END
                        CLOSE idx_cur
                        DEALLOCATE idx_cur
        
                        SET @LastIndexMaint = GETDATE()
                        UPDATE STATISTICS ', @FullSourceTableName, ' WITH SAMPLE 10 PERCENT
                        SET @LastStatsUpdate = GETDATE()
                        RAISERROR(''[%s] Maintenance done.'', 0, 1, @PrintDate) WITH NOWAIT;
                    END
                END ELSE IF @EnableAdaptiveStats = 1 BEGIN
                    DECLARE @ShouldUpdateStats BIT = 0
                    DECLARE @UpdateReason VARCHAR(100) = ''''
    
                    IF @BatchNum % @AdaptiveStatsBatchInterval = 0 BEGIN
                        /* Interval-based approach: Update every N batches regardless of performance */
                        SET @ShouldUpdateStats = 1
                        SET @UpdateReason = ''Interval reached''
                    END ELSE IF @AdaptiveStatsSlowdownThreshold > 0 AND DATEDIFF(MINUTE, @LastStatsUpdate, GETDATE()) >= 15 BEGIN
                        /* Slowdown-based approach: Update when performance degrades */
                        SET @SlowBatchThreshold = @MedianRecentSeconds * 2.0
                        IF @BatchElapsed > @SlowBatchThreshold BEGIN
                            SET @SlowDownCount += 1
                            IF @SlowDownCount >= @AdaptiveStatsSlowdownThreshold BEGIN
                                SET @ShouldUpdateStats = 1
                                SET @UpdateReason = ''Performance degradation''
                                SET @SlowDownCount = 0
                            END ELSE BEGIN
                                SET @PrintDate = FORMAT(GETDATE(), ''' + @DateFormat + ''')
                                RAISERROR(''[%s] Slowdown detected, not exceeding threshold yet. Continuing...'', 0, 1, @PrintDate) WITH NOWAIT;
                            END
                        END
                    END
    
                    IF @ShouldUpdateStats = 1 BEGIN
                        SET @PrintDate = FORMAT(GETDATE(), ''' + @DateFormat + ''')
                        RAISERROR(''[%s] %s - Updating statistics...'', 0, 1, @PrintDate, @UpdateReason) WITH NOWAIT;
        
                        UPDATE STATISTICS ' + @FullSourceTableName + ' WITH SAMPLE 10 PERCENT;
                        SET @LastStatsUpdate = GETDATE()
        
                        SET @PrintDate = FORMAT(GETDATE(), ''' + @DateFormat + ''')
                        RAISERROR(''[%s] Statistics updated.'', 0, 1, @PrintDate) WITH NOWAIT;
                    END
                END
            END
        END

        IF @HasIdentity = 1 BEGIN
            SET @PrintDate = FORMAT(GETDATE(), ''', @DateFormat, ''')
            RAISERROR(''[%s] Disabling identity insert for %s.'', 0, 1, @PrintDate, ''', @FullArchiveTableName, ''') WITH NOWAIT;
            SET IDENTITY_INSERT [', @ArchiveSchemaName, '].[', @TableName, '] OFF;
        END
        ');
        IF @Debug = 1 BEGIN
            /* select this out on debug as it is way to big for printing to get all of it */
            SELECT @SQL AS [DeleteLoopSQL]; 
            RAISERROR('%s', 0, 1, @SQL) WITH NOWAIT; 
        END
        SET @RowCount = 0;
        EXEC sys.sp_executesql @stmt = @SQL, 
            @params = N'@Debug BIT, @PossibleLoopCount INT, 
                @TotalRows INT, @BatchSize INT, 
                @EnableAdaptiveStats BIT, @AdaptiveStatsSlowdownThreshold INT, 
                @AdaptiveStatsBatchInterval INT, @EnableAdaptiveIndexMaint BIT, 
                @RowCount INT OUTPUT', 
            @Debug = @Debug, @PossibleLoopCount = @PossibleLoopCount, 
            @TotalRows = @TotalRows, @BatchSize = @NibblingDeletesBatchSize,
            @EnableAdaptiveStats = @EnableAdaptiveStats,
            @AdaptiveStatsSlowdownThreshold = @AdaptiveStatsSlowdownThreshold,
            @AdaptiveStatsBatchInterval = @AdaptiveStatsBatchInterval,
            @EnableAdaptiveIndexMaint = @EnableAdaptiveIndexMaint,
            @RowCount = @RowCount OUTPUT;

        SET @PrintDate = FORMAT(GETDATE(), @DateFormat)
        SET @Msg = CONCAT('[', @PrintDate, '] Moved ', CAST(@RowCount AS VARCHAR(20)), ' records to archive.');
        RAISERROR(@Msg, 0, 1) WITH NOWAIT;

        /**************************************************************************************/
        /**************************************************************************************/
        SET @PrintDate = FORMAT(GETDATE(), @DateFormat)
        RAISERROR('[%s] Updating statistics, let sql server decide the percentage to scan.', 0, 1, @PrintDate) WITH NOWAIT;
        SET @SQL = CONCAT('UPDATE STATISTICS ', @FullSourceTableName, '')
        IF @Debug = 1 BEGIN RAISERROR('%s', 0, 1, @SQL) WITH NOWAIT; END
        EXEC sys.sp_executesql @stmt = @SQL
    
        /**************************************************************************************/
        /**************************************************************************************/
        IF @DropArchiveTable = 1 BEGIN
            SET @PrintDate = FORMAT(GETDATE(), @DateFormat)
            RAISERROR('[%s] Dropping archive table.', 0, 1, @PrintDate) WITH NOWAIT;
            SET @SQL = CONCAT('DROP TABLE IF EXISTS ', @FullArchiveTableName, '')
            IF @Debug = 1 BEGIN RAISERROR('%s', 0, 1, @SQL) WITH NOWAIT; END
            EXEC sys.sp_executesql @stmt = @SQL
            SET @PrintDate = FORMAT(GETDATE(), @DateFormat)
            RAISERROR('[%s] Dropped archive table.', 0, 1, @PrintDate) WITH NOWAIT;
        END

        /**************************************************************************************/
        /**************************************************************************************/
        IF @SourceDBRecoveryModel <> 'SIMPLE' BEGIN
            /* WE NEED TO ENSURE WE RUN THIS EVEN IF AN ERROR OCCURS, THIS CODE HAS TO RUN FROM BOTH THE FAILURE AND SUCCESS PATH **/
            SET @PrintDate = FORMAT(GETDATE(), @DateFormat)
            RAISERROR('[%s] Resetting recovery model on the source DB [%s].', 0, 1, @PrintDate, @SourceDB) WITH NOWAIT;
            SET @SQL = CONCAT('ALTER DATABASE [', @SourceDB, '] SET RECOVERY ', @SourceDBRecoveryModel);
            IF @Debug = 1 BEGIN RAISERROR('%s', 0, 1, @SQL) WITH NOWAIT; END
            EXEC sys.sp_executesql @stmt = @SQL
        END

        /**************************************************************************************/
        /**************************************************************************************/
        /**************************************************************************************/
        /**************************************************************************************/
        SET @PrintDate = FORMAT(GETDATE(), @DateFormat)
        RAISERROR('[%s] Archive Process Completed Successfully.', 0, 1, @PrintDate) WITH NOWAIT;
    END TRY
    BEGIN CATCH
        WHILE @@TRANCOUNT > 0 BEGIN 
            RAISERROR('Rolling back transaction(s)', 0, 1) WITH NOWAIT;
            ROLLBACK TRAN; 
        END
        /**************************************************************************************/
        /**************************************************************************************/
        IF @SourceDBRecoveryModel <> 'SIMPLE' BEGIN
            /* WE NEED TO ENSURE WE RUN THIS EVEN IF AN ERROR OCCURS, THIS CODE HAS TO RUN FROM BOTH THE FAILURE AND SUCCESS PATH **/
            SET @PrintDate = FORMAT(GETDATE(), @DateFormat)
            RAISERROR('[%s] Resetting recovery model on the source DB [%s].', 0, 1, @PrintDate, @SourceDB) WITH NOWAIT;
            SET @SQL = CONCAT('ALTER DATABASE [', @SourceDB, '] SET RECOVERY ', @SourceDBRecoveryModel);
            IF @Debug = 1 BEGIN RAISERROR('%s', 0, 1, @SQL) WITH NOWAIT; END
            EXEC sys.sp_executesql @stmt = @SQL
        END
    
        ;THROW;
    END CATCH
END

GO 

EXEC sys.sp_MS_marksystemobject @objname = 'dbo.sp_ArchiveTableData';
GO


-- @SourceDB
IF EXISTS (SELECT 1 FROM sys.extended_properties WHERE major_id = OBJECT_ID('dbo.sp_ArchiveTableData') AND name = N'MS_Description' AND minor_id = (SELECT parameter_id FROM sys.parameters WHERE object_id = OBJECT_ID('dbo.sp_ArchiveTableData') AND name = '@SourceDB'))
    EXEC sys.sp_dropextendedproperty @name = N'MS_Description', @level0type = N'SCHEMA', @level0name = 'dbo', @level1type = N'PROCEDURE', @level1name = 'sp_ArchiveTableData', @level2type = N'PARAMETER', @level2name = '@SourceDB';
EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'Source database name containing the table to archive', @level0type = N'SCHEMA', @level0name = 'dbo', @level1type = N'PROCEDURE', @level1name = 'sp_ArchiveTableData', @level2type = N'PARAMETER', @level2name = '@SourceDB';

-- @SourceSchemaName
IF EXISTS (SELECT 1 FROM sys.extended_properties WHERE major_id = OBJECT_ID('dbo.sp_ArchiveTableData') AND name = N'MS_Description' AND minor_id = (SELECT parameter_id FROM sys.parameters WHERE object_id = OBJECT_ID('dbo.sp_ArchiveTableData') AND name = '@SourceSchemaName'))
    EXEC sys.sp_dropextendedproperty @name = N'MS_Description', @level0type = N'SCHEMA', @level0name = 'dbo', @level1type = N'PROCEDURE', @level1name = 'sp_ArchiveTableData', @level2type = N'PARAMETER', @level2name = '@SourceSchemaName';
EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'Schema name of the source table (default: dbo)', @level0type = N'SCHEMA', @level0name = 'dbo', @level1type = N'PROCEDURE', @level1name = 'sp_ArchiveTableData', @level2type = N'PARAMETER', @level2name = '@SourceSchemaName';

-- @ArchiveDB
IF EXISTS (SELECT 1 FROM sys.extended_properties WHERE major_id = OBJECT_ID('dbo.sp_ArchiveTableData') AND name = N'MS_Description' AND minor_id = (SELECT parameter_id FROM sys.parameters WHERE object_id = OBJECT_ID('dbo.sp_ArchiveTableData') AND name = '@ArchiveDB'))
    EXEC sys.sp_dropextendedproperty @name = N'MS_Description', @level0type = N'SCHEMA', @level0name = 'dbo', @level1type = N'PROCEDURE', @level1name = 'sp_ArchiveTableData', @level2type = N'PARAMETER', @level2name = '@ArchiveDB';
EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'Archive database name (default: [SourceDB]_Archive). Will be created if it does not exist', @level0type = N'SCHEMA', @level0name = 'dbo', @level1type = N'PROCEDURE', @level1name = 'sp_ArchiveTableData', @level2type = N'PARAMETER', @level2name = '@ArchiveDB';

-- @ArchiveSchemaName
IF EXISTS (SELECT 1 FROM sys.extended_properties WHERE major_id = OBJECT_ID('dbo.sp_ArchiveTableData') AND name = N'MS_Description' AND minor_id = (SELECT parameter_id FROM sys.parameters WHERE object_id = OBJECT_ID('dbo.sp_ArchiveTableData') AND name = '@ArchiveSchemaName'))
    EXEC sys.sp_dropextendedproperty @name = N'MS_Description', @level0type = N'SCHEMA', @level0name = 'dbo', @level1type = N'PROCEDURE', @level1name = 'sp_ArchiveTableData', @level2type = N'PARAMETER', @level2name = '@ArchiveSchemaName';
EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'Schema name for the archive table (default: dbo). Required to be different from source schema if archiving to same database', @level0type = N'SCHEMA', @level0name = 'dbo', @level1type = N'PROCEDURE', @level1name = 'sp_ArchiveTableData', @level2type = N'PARAMETER', @level2name = '@ArchiveSchemaName';

-- @TableName
IF EXISTS (SELECT 1 FROM sys.extended_properties WHERE major_id = OBJECT_ID('dbo.sp_ArchiveTableData') AND name = N'MS_Description' AND minor_id = (SELECT parameter_id FROM sys.parameters WHERE object_id = OBJECT_ID('dbo.sp_ArchiveTableData') AND name = '@TableName'))
    EXEC sys.sp_dropextendedproperty @name = N'MS_Description', @level0type = N'SCHEMA', @level0name = 'dbo', @level1type = N'PROCEDURE', @level1name = 'sp_ArchiveTableData', @level2type = N'PARAMETER', @level2name = '@TableName';
EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'Table name to archive (must have a primary key). Archive table will use the same name', @level0type = N'SCHEMA', @level0name = 'dbo', @level1type = N'PROCEDURE', @level1name = 'sp_ArchiveTableData', @level2type = N'PARAMETER', @level2name = '@TableName';

-- @AdditionalSQL
IF EXISTS (SELECT 1 FROM sys.extended_properties WHERE major_id = OBJECT_ID('dbo.sp_ArchiveTableData') AND name = N'MS_Description' AND minor_id = (SELECT parameter_id FROM sys.parameters WHERE object_id = OBJECT_ID('dbo.sp_ArchiveTableData') AND name = '@AdditionalSQL'))
    EXEC sys.sp_dropextendedproperty @name = N'MS_Description', @level0type = N'SCHEMA', @level0name = 'dbo', @level1type = N'PROCEDURE', @level1name = 'sp_ArchiveTableData', @level2type = N'PARAMETER', @level2name = '@AdditionalSQL';
EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'WHERE clause or JOIN conditions for filtering the source tables rows to archive. Example: ''WHERE CreatedDate < ''''2023-01-01''''''. If NULL, archives entire table', @level0type = N'SCHEMA', @level0name = 'dbo', @level1type = N'PROCEDURE', @level1name = 'sp_ArchiveTableData', @level2type = N'PARAMETER', @level2name = '@AdditionalSQL';

-- @BackupSourceDB
IF EXISTS (SELECT 1 FROM sys.extended_properties WHERE major_id = OBJECT_ID('dbo.sp_ArchiveTableData') AND name = N'MS_Description' AND minor_id = (SELECT parameter_id FROM sys.parameters WHERE object_id = OBJECT_ID('dbo.sp_ArchiveTableData') AND name = '@BackupSourceDB'))
    EXEC sys.sp_dropextendedproperty @name = N'MS_Description', @level0type = N'SCHEMA', @level0name = 'dbo', @level1type = N'PROCEDURE', @level1name = 'sp_ArchiveTableData', @level2type = N'PARAMETER', @level2name = '@BackupSourceDB';
EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'If 1, creates a copy-only backup before archiving (skipped if a copy-only backup exists within last 24 hours)', @level0type = N'SCHEMA', @level0name = 'dbo', @level1type = N'PROCEDURE', @level1name = 'sp_ArchiveTableData', @level2type = N'PARAMETER', @level2name = '@BackupSourceDB';

-- @CreateArchivePK
IF EXISTS (SELECT 1 FROM sys.extended_properties WHERE major_id = OBJECT_ID('dbo.sp_ArchiveTableData') AND name = N'MS_Description' AND minor_id = (SELECT parameter_id FROM sys.parameters WHERE object_id = OBJECT_ID('dbo.sp_ArchiveTableData') AND name = '@CreateArchivePK'))
    EXEC sys.sp_dropextendedproperty @name = N'MS_Description', @level0type = N'SCHEMA', @level0name = 'dbo', @level1type = N'PROCEDURE', @level1name = 'sp_ArchiveTableData', @level2type = N'PARAMETER', @level2name = '@CreateArchivePK';
EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'If 1, creates primary key on archive table (ignored when @DropArchiveTable = 1)', @level0type = N'SCHEMA', @level0name = 'dbo', @level1type = N'PROCEDURE', @level1name = 'sp_ArchiveTableData', @level2type = N'PARAMETER', @level2name = '@CreateArchivePK';

-- @DropArchiveTable
IF EXISTS (SELECT 1 FROM sys.extended_properties WHERE major_id = OBJECT_ID('dbo.sp_ArchiveTableData') AND name = N'MS_Description' AND minor_id = (SELECT parameter_id FROM sys.parameters WHERE object_id = OBJECT_ID('dbo.sp_ArchiveTableData') AND name = '@DropArchiveTable'))
    EXEC sys.sp_dropextendedproperty @name = N'MS_Description', @level0type = N'SCHEMA', @level0name = 'dbo', @level1type = N'PROCEDURE', @level1name = 'sp_ArchiveTableData', @level2type = N'PARAMETER', @level2name = '@DropArchiveTable';
EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'If 1, drops archive table after completion (use for permanent deletion instead of preservation)', @level0type = N'SCHEMA', @level0name = 'dbo', @level1type = N'PROCEDURE', @level1name = 'sp_ArchiveTableData', @level2type = N'PARAMETER', @level2name = '@DropArchiveTable';

-- @DataPath
IF EXISTS (SELECT 1 FROM sys.extended_properties WHERE major_id = OBJECT_ID('dbo.sp_ArchiveTableData') AND name = N'MS_Description' AND minor_id = (SELECT parameter_id FROM sys.parameters WHERE object_id = OBJECT_ID('dbo.sp_ArchiveTableData') AND name = '@DataPath'))
    EXEC sys.sp_dropextendedproperty @name = N'MS_Description', @level0type = N'SCHEMA', @level0name = 'dbo', @level1type = N'PROCEDURE', @level1name = 'sp_ArchiveTableData', @level2type = N'PARAMETER', @level2name = '@DataPath';
EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'File path for archive database data files (only used if archive database needs to be created)', @level0type = N'SCHEMA', @level0name = 'dbo', @level1type = N'PROCEDURE', @level1name = 'sp_ArchiveTableData', @level2type = N'PARAMETER', @level2name = '@DataPath';

-- @LogPath
IF EXISTS (SELECT 1 FROM sys.extended_properties WHERE major_id = OBJECT_ID('dbo.sp_ArchiveTableData') AND name = N'MS_Description' AND minor_id = (SELECT parameter_id FROM sys.parameters WHERE object_id = OBJECT_ID('dbo.sp_ArchiveTableData') AND name = '@LogPath'))
    EXEC sys.sp_dropextendedproperty @name = N'MS_Description', @level0type = N'SCHEMA', @level0name = 'dbo', @level1type = N'PROCEDURE', @level1name = 'sp_ArchiveTableData', @level2type = N'PARAMETER', @level2name = '@LogPath';
EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'File path for archive database log files (only used if archive database needs to be created)', @level0type = N'SCHEMA', @level0name = 'dbo', @level1type = N'PROCEDURE', @level1name = 'sp_ArchiveTableData', @level2type = N'PARAMETER', @level2name = '@LogPath';

-- @NibblingDeletesBatchSize
IF EXISTS (SELECT 1 FROM sys.extended_properties WHERE major_id = OBJECT_ID('dbo.sp_ArchiveTableData') AND name = N'MS_Description' AND minor_id = (SELECT parameter_id FROM sys.parameters WHERE object_id = OBJECT_ID('dbo.sp_ArchiveTableData') AND name = '@NibblingDeletesBatchSize'))
    EXEC sys.sp_dropextendedproperty @name = N'MS_Description', @level0type = N'SCHEMA', @level0name = 'dbo', @level1type = N'PROCEDURE', @level1name = 'sp_ArchiveTableData', @level2type = N'PARAMETER', @level2name = '@NibblingDeletesBatchSize';
EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'Rows per batch for deletes. If 0 or negative, automatically calculates safe batch size to prevent lock escalation (~80% of 5000 lock threshold). Recommended: leave at 0. A minimum of 1000 is enforced.', @level0type = N'SCHEMA', @level0name = 'dbo', @level1type = N'PROCEDURE', @level1name = 'sp_ArchiveTableData', @level2type = N'PARAMETER', @level2name = '@NibblingDeletesBatchSize';

-- @SourceDBRecoveryModel
IF EXISTS (SELECT 1 FROM sys.extended_properties WHERE major_id = OBJECT_ID('dbo.sp_ArchiveTableData') AND name = N'MS_Description' AND minor_id = (SELECT parameter_id FROM sys.parameters WHERE object_id = OBJECT_ID('dbo.sp_ArchiveTableData') AND name = '@SourceDBRecoveryModel'))
    EXEC sys.sp_dropextendedproperty @name = N'MS_Description', @level0type = N'SCHEMA', @level0name = 'dbo', @level1type = N'PROCEDURE', @level1name = 'sp_ArchiveTableData', @level2type = N'PARAMETER', @level2name = '@SourceDBRecoveryModel';
EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'Recovery model to restore on source database after completion (FULL, SIMPLE, or BULK_LOGGED)', @level0type = N'SCHEMA', @level0name = 'dbo', @level1type = N'PROCEDURE', @level1name = 'sp_ArchiveTableData', @level2type = N'PARAMETER', @level2name = '@SourceDBRecoveryModel';

-- @EnableAdaptiveStats
IF EXISTS (SELECT 1 FROM sys.extended_properties WHERE major_id = OBJECT_ID('dbo.sp_ArchiveTableData') AND name = N'MS_Description' AND minor_id = (SELECT parameter_id FROM sys.parameters WHERE object_id = OBJECT_ID('dbo.sp_ArchiveTableData') AND name = '@EnableAdaptiveStats'))
    EXEC sys.sp_dropextendedproperty @name = N'MS_Description', @level0type = N'SCHEMA', @level0name = 'dbo', @level1type = N'PROCEDURE', @level1name = 'sp_ArchiveTableData', @level2type = N'PARAMETER', @level2name = '@EnableAdaptiveStats';
EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'If 1, automatically updates statistics when batch performance degrades significantly. Disabled by default - can slow down process. Use only if severe performance degradation occurs', @level0type = N'SCHEMA', @level0name = 'dbo', @level1type = N'PROCEDURE', @level1name = 'sp_ArchiveTableData', @level2type = N'PARAMETER', @level2name = '@EnableAdaptiveStats';

-- @AdaptiveStatsSlowdownThreshold
IF EXISTS (SELECT 1 FROM sys.extended_properties WHERE major_id = OBJECT_ID('dbo.sp_ArchiveTableData') AND name = N'MS_Description' AND minor_id = (SELECT parameter_id FROM sys.parameters WHERE object_id = OBJECT_ID('dbo.sp_ArchiveTableData') AND name = '@AdaptiveStatsSlowdownThreshold'))
    EXEC sys.sp_dropextendedproperty @name = N'MS_Description', @level0type = N'SCHEMA', @level0name = 'dbo', @level1type = N'PROCEDURE', @level1name = 'sp_ArchiveTableData', @level2type = N'PARAMETER', @level2name = '@AdaptiveStatsSlowdownThreshold';
EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'Number of consecutive slow batches required before triggering statistics update (performance-based trigger). Only applies when @EnableAdaptiveStats = 1. Use 0 to disable. Cannot be used together with @AdaptiveStatsBatchInterval', @level0type = N'SCHEMA', @level0name = 'dbo', @level1type = N'PROCEDURE', @level1name = 'sp_ArchiveTableData', @level2type = N'PARAMETER', @level2name = '@AdaptiveStatsSlowdownThreshold';

-- @AdaptiveStatsBatchInterval
IF EXISTS (SELECT 1 FROM sys.extended_properties WHERE major_id = OBJECT_ID('dbo.sp_ArchiveTableData') AND name = N'MS_Description' AND minor_id = (SELECT parameter_id FROM sys.parameters WHERE object_id = OBJECT_ID('dbo.sp_ArchiveTableData') AND name = '@AdaptiveStatsBatchInterval'))
    EXEC sys.sp_dropextendedproperty @name = N'MS_Description', @level0type = N'SCHEMA', @level0name = 'dbo', @level1type = N'PROCEDURE', @level1name = 'sp_ArchiveTableData', @level2type = N'PARAMETER', @level2name = '@AdaptiveStatsBatchInterval';
EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'Number of batches between statistics updates (interval-based trigger). Only applies when @EnableAdaptiveStats = 1. Use 0 to disable. Cannot be used together with @AdaptiveStatsSlowdownThreshold. Must also be greater than 5.', @level0type = N'SCHEMA', @level0name = 'dbo', @level1type = N'PROCEDURE', @level1name = 'sp_ArchiveTableData', @level2type = N'PARAMETER', @level2name = '@AdaptiveStatsBatchInterval';

-- @EnableAdaptiveIndexMaint
IF EXISTS (SELECT 1 FROM sys.extended_properties WHERE major_id = OBJECT_ID('dbo.sp_ArchiveTableData') AND name = N'MS_Description' AND minor_id = (SELECT parameter_id FROM sys.parameters WHERE object_id = OBJECT_ID('dbo.sp_ArchiveTableData') AND name = '@EnableAdaptiveIndexMaint'))
    EXEC sys.sp_dropextendedproperty @name = N'MS_Description', @level0type = N'SCHEMA', @level0name = 'dbo', @level1type = N'PROCEDURE', @level1name = 'sp_ArchiveTableData', @level2type = N'PARAMETER', @level2name = '@EnableAdaptiveIndexMaint';
EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'If 1, performs index reorganization when fragmentation exceeds 40%. NOT RECOMMENDED - can cause severe slowdowns and schema locks. Better to handle index maintenance separately', @level0type = N'SCHEMA', @level0name = 'dbo', @level1type = N'PROCEDURE', @level1name = 'sp_ArchiveTableData', @level2type = N'PARAMETER', @level2name = '@EnableAdaptiveIndexMaint';

-- @Debug
IF EXISTS (SELECT 1 FROM sys.extended_properties WHERE major_id = OBJECT_ID('dbo.sp_ArchiveTableData') AND name = N'MS_Description' AND minor_id = (SELECT parameter_id FROM sys.parameters WHERE object_id = OBJECT_ID('dbo.sp_ArchiveTableData') AND name = '@Debug'))
    EXEC sys.sp_dropextendedproperty @name = N'MS_Description', @level0type = N'SCHEMA', @level0name = 'dbo', @level1type = N'PROCEDURE', @level1name = 'sp_ArchiveTableData', @level2type = N'PARAMETER', @level2name = '@Debug';
EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'If 1, prints all dynamic SQL statements for troubleshooting without executing them', @level0type = N'SCHEMA', @level0name = 'dbo', @level1type = N'PROCEDURE', @level1name = 'sp_ArchiveTableData', @level2type = N'PARAMETER', @level2name = '@Debug';

PRINT 'Extended properties added/updated successfully. Press Ctrl+Shift+R in SSMS to refresh IntelliSense.';