The script you provided is logically correct for renaming columns in a system-versioned table. However, regarding your question about safety and minimizing downtime on a "quite big" table, there are two critical risks you need to address: locking blocking chains and data consistency checks.

Is it safe?

Yes, but it will cause a brief "stop-the-world" moment for that table.

• The Lock: sp_rename and ALTER TABLE require a Schema Modification Lock (Sch-M).
• The Impact: While this transaction is open, ALL access to AccountBalance (SELECT, INSERT, UPDATE) will be completely blocked.
• The Risk: If a long-running query is executing when you run this script, your script will wait behind it. While your script waits, it blocks all new incoming queries, potentially causing a massive pile-up (blocking chain) even before your script actually runs.

How to minimize downtime

To ensure the downtime is truly just "a few ms," you should modify your script to include a lock timeout and disable the default consistency check.

1. Add DATA_CONSISTENCY_CHECK = OFF

By default, when you turn SYSTEM_VERSIONING = ON, SQL Server performs a full scan of the table to ensure the temporal data is consistent. On a large table, this could take minutes or hours, holding the exclusive lock the entire time. Since you are only renaming a column inside a transaction and not modifying data, you can safely skip this check.

2. Add SET LOCK_TIMEOUT

To prevent your migration from inadvertently causing a pile-up if it gets stuck behind a stray query, set a timeout. If it can't get the exclusive lock within 5 seconds, it's safer for it to fail than to queue up 500 web requests behind it.

Recommended Script

Here is the optimized version of your script:

    -- Safety: Stop trying if we can't get the lock within 5 seconds
    -- This prevents the migration from blocking valid traffic while it waits for a straggler query
    SET LOCK_TIMEOUT 5000;

    BEGIN TRAN
    -- 1. Turn off versioning (Exclusive Lock acquired here)
    -- This separates the current table from the history table
    ALTER TABLE dbo.AccountBalance SET (SYSTEM_VERSIONING = OFF);

    -- 2. Rename the column in the main table
    -- This is a metadata-only operation (very fast)
    EXEC sp_rename 'dbo.AccountBalance.TransactionId', 'ExternalTransactionId', 'COLUMN';

    -- 3. Rename the column in the history table
    -- Required to maintain schema parity
    EXEC sp_rename 'dbo.AccountBalanceHistory.TransactionId', 'ExternalTransactionId', 'COLUMN';

    -- 4. Turn versioning back on WITHOUT a full data scan
    -- CRITICAL: DATA_CONSISTENCY_CHECK = OFF prevents a full table scan
    ALTER TABLE dbo.AccountBalance
    SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.AccountBalanceHistory, DATA_CONSISTENCY_CHECK = OFF));

    COMMIT TRAN
    

Summary of Execution Plan

1. Check for active transactions: Before running, run sp_whoisactive or check sys.dm_tran_locks to ensure no long-running reports are touching dbo.AccountBalance.
2. Run the script: With DATA_CONSISTENCY_CHECK = OFF, the execution time will be dominated purely by metadata updates (milliseconds), regardless of table size.
3. Verify: Check that versioning is active and the column names are updated.

Analysis of Your Schema Change on Azure SQL

This query carries real risk of blocking other traffic, even during low-traffic hours. Let me break down why and how to mitigate it.

---

What Happens Under the Hood

1. SYSTEM_VERSIONING = OFF — Acquires a Schema Modification Lock (Sch-M)

This requires an exclusive Sch-M lock on dbo.AccountBalance. A Sch-M lock is incompatible with every other lock type, including the Sch-S (Schema Stability) locks that even simple SELECT queries take.

2. sp_rename — Also requires Sch-M locks

Each rename acquires a Sch-M lock on the respective table. You're renaming on both the main and history tables.

3. SYSTEM_VERSIONING = ON — Another Sch-M lock

Re-enabling versioning also requires schema modification locks and performs a consistency check between the two tables.

4. All inside a single explicit transaction

This is the critical problem. The Sch-M lock acquired by the first statement is held for the entire duration of the transaction — across all four statements. This maximizes the lock window.

---

The Danger: Lock Queuing & Convoy Effect

Even with "a few queries per minute, each only a few ms":

1. Your ALTER needs a Sch-M lock, so it waits for any in-flight queries to finish (they hold Sch-S locks).
2. While your ALTER is waiting, it queues a Sch-M request.
3. All new queries queue behind the pending Sch-M lock — they can't get Sch-S while Sch-M is waiting.
4. This creates a convoy effect: even one long-running query ahead of you blocks you, and you block everyone behind you.

⚠️ The wrapping BEGIN TRAN / COMMIT TRAN makes this significantly worse because it holds Sch-M across all four operations instead of acquiring and releasing it for each independently.

---

Recommendations to Minimize Impact

Option 1: Remove the Explicit Transaction (Preferred)

Run each statement independently and rely on short, individual lock windows:

-- Step 1: Turn off system versioning (brief Sch-M lock, then released)
ALTER TABLE dbo.AccountBalance SET (SYSTEM_VERSIONING = OFF);
GO

-- Step 2: Rename column on main table (brief Sch-M lock)
EXEC sp_rename 'dbo.AccountBalance.TransactionId', 'ExternalTransactionId', 'COLUMN';
GO

-- Step 3: Rename column on history table (brief Sch-M lock)
EXEC sp_rename 'dbo.AccountBalanceHistory.TransactionId', 'ExternalTransactionId', 'COLUMN';
GO

-- Step 4: Re-enable system versioning (brief Sch-M lock)
ALTER TABLE dbo.AccountBalance SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.AccountBalanceHistory));
GO

Why this is better: Each statement acquires and releases its Sch-M lock independently (milliseconds each), rather than holding one across all four operations.

Trade-off: Between steps 1 and 4, the table is not system-versioned. This means:

• No temporal history is recorded for changes in that window
• The history table is a standalone table briefly (no FK-like enforcement)
• If step 3 or 4 fails, you need to manually recover

This is usually acceptable for a rename that takes < 1 second total.

Option 2: Use Lock Timeout to Avoid Convoy

If you want to keep the transaction for atomicity, set a short lock timeout so you fail fast instead of causing a convoy:

SET LOCK_TIMEOUT 5000; -- Fail after 5 seconds instead of waiting indefinitely

BEGIN TRAN
    ALTER TABLE dbo.AccountBalance SET (SYSTEM_VERSIONING = OFF);
    EXEC sp_rename 'dbo.AccountBalance.TransactionId', 'ExternalTransactionId', 'COLUMN';
    EXEC sp_rename 'dbo.AccountBalanceHistory.TransactionId', 'ExternalTransactionId', 'COLUMN';
    ALTER TABLE dbo.AccountBalance SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.AccountBalanceHistory));
COMMIT TRAN

If it times out, retry — no harm done.

Additional Safeguards

• Kill long-running queries on the table before executing (sp_who2, sys.dm_exec_requests)
• Check for open transactions first: DBCC OPENTRAN
• Avoid running during scheduled jobs that might touch AccountBalance

---

TL;DR

Aspect	Your Original Script	Recommended (Option 1)
Lock duration	Entire transaction	Milliseconds per statement
Convoy risk	High	Minimal
Atomicity	Full rollback	Manual recovery if mid-way failure
Practical risk	Low-traffic ≠ no-traffic	Safe even with concurrent queries

Go with Option 1 unless you have a strong need for atomicity. For a column rename, the risk of a partial failure is far lower than the risk of a lock convoy in production.