Skip to content

v3.3.2 - Diag PK_runs fix

Choose a tag to compare

View all tags
@nanoDBA nanoDBA released this 20 Apr 22:58
· 66 commits to main since this release
v3.3.2
79bb7b9

Fix

`sp_StatUpdate_Diag` aborted with:

```
Msg 2627, Level 14, State 1
Violation of PRIMARY KEY constraint 'PK_runs'.
Cannot insert duplicate key in object 'dbo.#runs'.
```

Root cause: the `INSERT INTO #runs` LEFT JOINs `SP_STATUPDATE_START` to `SP_STATUPDATE_END` on `RunLabel`. `CommandLog` can contain multiple ENDs per RunLabel (orphan-cleanup KILLED record from gh-425 plus a later real END) or duplicate STARTs (same-second retries). The existing dedup at line ~1373 would have handled this, but the PK constraint blocked the INSERT before dedup could run.

Change (diag v2026.04.20.1)

  • Removed `CONSTRAINT PK_runs` from `#runs` `CREATE TABLE`.
  • Added `UX_runs_RunLabel` unique index after the dedup CTE -- same uniqueness guarantee and index benefit for downstream joins, just enforced post-dedup.
  • Dedup `ORDER BY` now `StartTime DESC, EndTime DESC` so a real END is preferred over a KILLED orphan record when both exist for the same RunLabel.

Bundled

Also includes the v3.3.1 proc-deploy fix for `dbo.QueueStatistic` schema upgrade (pre-`ALTER PROCEDURE` migration batches for `ClaimLoginTime` and `LastStatCompletedAt`).

Upgrade path

Drop-in replacement. Re-deploy `sp_StatUpdate_Diag.sql` and `sp_StatUpdate.sql` -- both are idempotent.

Assets 2
Loading