Skip to content

URL Shrink Upgrade Runbook

Sam Betts edited this page Nov 4, 2026 · 3 revisions

Updating the solution → URLs Full URL Shrink Upgrade → URL Shrink Upgrade Runbook

This page is for administrators running the URL-column upgrade from a build before 1634. It lists what to do before, during and after the upgrade, and what to do if the migration stops. Size your maintenance window first, with the sizing page.

5. Pre-upgrade recommendations

Do these before you start the upgrade, in this order:

5.1 Stop the analytics web jobs

The web jobs continuously hit urls from the staging-table merges. Leaving them running during the migration will (a) make the ALTER COLUMN much slower because of the schema-modification lock contention, and (b) almost certainly cause the web jobs themselves to time out and fail.

  • In the Azure portal, open the App Service → WebJobs and stop Office365ActivityImporter and AppInsightsImporter.
  • Wait ~30 seconds for any in-flight batches to finish committing.

5.2 Pause / unschedule Power BI dataset refreshes

A Power BI refresh against a busy urls/hits/audit_events workload during the migration will:

  • Compete for IO with the ALTER COLUMN rewrite and the CREATE INDEX sort.
  • Probably fail anyway because the new index/column type temporarily change query plans.

Open the dataset(s) in the Power BI Service, disable scheduled refresh (or change the schedule to skip the upgrade window), and cancel any refresh that's already in progress.

5.3 Free up disk / data-file headroom

This migration is storage-intensive. Post-migration urls grows because the URLs move from LOB pages into the clustered PK leaf pages and are now stored as 2-byte-per-char nvarchar. On the superseded varchar(1700) form this measured ~2.5× the pre-migration size; for nvarchar(850) budget roughly 3.5–4× (the benchmark figures were measured on varchar). Before kicking off:

  • Data file: ensure the data file (or auto-grow) has headroom for ~4 × current urls size. Example: a 30 GB urls table can need ~120 GB total post-migration, so ~90 GB free in the data file (or in the volume the data file can grow into).
  • Transaction log: allow free log space on the order of 2–3 × urls data size. On SIMPLE recovery the log auto-truncates between steps; on FULL recovery take a log backup before the upgrade and another straight after.
  • tempdb: the CREATE INDEX sort spills to tempdb. Make sure tempdb has at least ~2 GB free per 10M URLs being sorted.

5.4 Temporarily scale up your Azure SQL Database

If your DB is on a lower DTU tier (S3 / S4 / S6) and you have more than ~25M urls, scale up before the upgrade. The cost of running S7 (or even Premium) for a few hours is trivial compared to the time saved.

A reasonable pattern:

  1. Scale up to S9 (1600 DTU) or a Premium P2 / P4 tier just before starting.
  2. Apply the migration.
  3. Verify the new column type and index are present (see §8).
  4. Scale back down to your normal tier.

Scaling Azure SQL DB up or down is online — your DB stays available during the resize, with a brief (single-digit-second) reconnect at the end. Plan to do the scale-up at least a few minutes before kicking off the migration so it's settled in.

5.5 Make sure you have a recent backup

Azure SQL DB takes automatic backups, but confirm the most recent backup is recent (last few hours) before starting. If anything goes wrong you want a clean rollback point.

5.6 Allow plenty of time in your maintenance window

Use the estimates in §3 and §4 of the sizing page and multiply by 2 when picking the maintenance window. Things you don't want to discover at the 90% mark:

  • The DB was busier than you thought.
  • A long-running query held a lock on urls and the ALTER COLUMN was waiting on it.
  • The auto-generated tempdb / log file growth was throttled.
  • The pre-flight scans found offending URLs and aborted, and now you need to fix them and re-run.

6. If the migration aborts (URLs longer than 850 chars)

If the pre-flight scan finds any URL that would be truncated (>850 chars), the migration aborts before changing anything and prints up to 50 offending rows on the SQL session messages. The database is left in exactly the state it was in before — there is no partial-apply state to clean up. (Non-Latin characters such as Greek do not abort the migration — nvarchar(850) stores them faithfully; only length matters.)

To find every offending row:

-- Too long: would be truncated to 850 chars
SELECT id, LEN(full_url) AS length, full_url
FROM dbo.urls
WHERE LEN(full_url) > 850
ORDER BY length DESC;

For each offending row, decide whether to:

  • Delete the URL row (and the hits / event_meta_sharepoint / etc. rows that reference it via url_id). Usually appropriate for obsolete or test URLs.
  • Correct the URL — e.g. trim the query string that pushed it past 850 chars.

Then re-run the upgrade. The migration is idempotent: it will pick up where the previous run left off.


7. During the upgrade

  • Watch the SQL session running the migration. The script uses RAISERROR ... WITH NOWAIT to surface live progress — you'll see messages like:
    ShrinkUrlsFullUrlColumn: dbo.urls row estimate = 10000000 ...
    ShrinkUrlsFullUrlColumn: pre-flight "too long" scan completed in 2511ms; 0 offending row(s).
    ShrinkUrlsFullUrlColumn: altering full_url to nvarchar(850) NOT NULL...
    ShrinkUrlsFullUrlColumn: ALTER COLUMN completed in 132690ms.
    ShrinkUrlsFullUrlColumn: EngineEdition=2, ONLINE=0. Creating [IX_urls_full_url]...
    ShrinkUrlsFullUrlColumn: [IX_urls_full_url] created in 189933ms.
    ShrinkUrlsFullUrlColumn: finished in 325136ms.
    
  • It is safe to leave it running, however long it takes. The installer's EF migration runner sets CommandTimeout = 0 (infinite), so the upgrade will not give up part-way through ALTER COLUMN even if it runs for many hours. The migration also runs outside the EF migration transaction (suppressTransaction: true), so each step commits as it completes. If the connection drops mid-way through CREATE INDEX, you can simply re-run the upgrade — the early steps are idempotent (they check whether they've already been done) and SQL Server will rebuild any partially-built index from scratch.

8. After the upgrade — quick sanity check

Run this against your DB:

SELECT
    (SELECT COUNT_BIG(*) FROM dbo.urls) AS urls_total,
    (SELECT t.name + '(' + CASE WHEN c.max_length = -1 THEN 'max'
              WHEN t.name LIKE 'n%' THEN CAST(c.max_length / 2 AS varchar(10))
              ELSE CAST(c.max_length AS varchar(10)) END + ')'
       FROM sys.columns c
       INNER JOIN sys.types t ON c.user_type_id = t.user_type_id
       WHERE c.object_id = OBJECT_ID('dbo.urls') AND c.name = 'full_url'
    )                                                                AS full_url_type,
    -- should be 'nvarchar(850)'
    (SELECT COUNT(*) FROM sys.indexes
       WHERE object_id = OBJECT_ID('dbo.urls') AND name = 'IX_urls_full_url'
    )                                                                AS ix_present,
    -- should be 1
    (SELECT COUNT_BIG(*) FROM dbo.urls WHERE LEN(full_url) > 850)    AS still_too_long;
    -- should be 0

Expected: full_url_type = 'nvarchar(850)', ix_present = 1, still_too_long = 0, urls_total unchanged from before. If the same upgrade also ran Stable 1830's UniqueUrlsFullUrlIndex, as an upgrade from before 1626 to a current build does, urls_total falls by the number of duplicate URL rows it removed: see What it does NOT do.

Then check that the IX_urls_full_url index is actually being used by the staging merges (this is the whole reason for the migration). After the first importer run completes, the plan for the staging-merge join should show an index seek on IX_urls_full_url rather than a clustered-index scan. The simplest way to confirm:

SELECT TOP 5
    s.user_seeks, s.user_scans, s.last_user_seek, s.last_user_scan
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON s.object_id = i.object_id AND s.index_id = i.index_id
WHERE s.database_id = DB_ID()
  AND i.name = 'IX_urls_full_url';

user_seeks should be incrementing every time the App Insights / Activity / Copilot importers run.

Once that's confirmed:

  1. Re-enable Power BI scheduled refresh.
  2. Restart the web jobs.
  3. Scale the DB back down to its normal tier.

Return to URLs Full URL Shrink Upgrade · All topics in this guide

Previous: URL Shrink Upgrade Sizing · Next: Audit Events FK Upgrade

Start here

Copilot analytics

Using the portal

Install & configure

Beyond Copilot

Operate

Upgrading from an older build

Reference

Project

Clone this wiki locally