dohertj2/lmxopcua
1
0
Fork 0
Code Issues 14 Pull Requests Actions Packages Projects Releases Wiki Activity

historian(sqlserver): retention / purge for hist.Sample and hist.Event #667

New Issue
Closed
opened 2026-10-09 15:26:03 -04:00 by dohertj2 · 1 comment
dohertj2 commented 2026-10-09 15:26:03 -04:00
Owner
Copy Link
Copy Source

The SQL Server historian backend (Driver.Historian.SqlServer, schema hist) has no retention or purge, so hist.Sample and hist.Event grow without bound.

Scope: configurable retention (for example ServerHistorian:SqlServer:RetentionDays), applied by a Primary-only background purge in batched deletes on the clustered key, so the log doesn't blow up. Consider partition switching for large installs, and add a metric for rows purged.

Claude-Session: https://claude.ai/code/session_01Ef2ozxS7QPanGT8uoATbTe

The SQL Server historian backend (`Driver.Historian.SqlServer`, schema `hist`) has no retention or purge, so `hist.Sample` and `hist.Event` grow without bound. **Scope:** configurable retention (for example `ServerHistorian:SqlServer:RetentionDays`), applied by a Primary-only background purge in batched deletes on the clustered key, so the log doesn't blow up. Consider partition switching for large installs, and add a metric for rows purged. Claude-Session: https://claude.ai/code/session_01Ef2ozxS7QPanGT8uoATbTe
dohertj2 commented 2026-10-11 10:17:58 -04:00
Author
Owner
Copy Link
Copy Source

Implemented in b3db64af, live-gate fix 984e069f (both merged to master).

  • Opt-in ServerHistorian:SqlServer:RetentionDays (null = keep forever) + PurgeInterval (1 h) + PurgeBatchSize (4000).
  • HistorianRetentionService purges hist.Sample per tag in DELETE TOP(n) batches on the clustered key and hist.Event by time; cutoff on source time. Primary-gated on IHistorizationRoleView; one purger per database via sp_getapplock (timeout 0 — a loser skips the pass). Metric otopcua.historian.retention.purged{table}. Optional IHistorianBackendProvider.CreateRetention (default null; QuestDB keeps its TTL). Since #668 it also purges hist.SampleModified.
  • Pooling trap handled: a session-owned applock survives sp_reset_connection, so a failed release clears the pool.

Live gates (docker-dev): gate 1 — purge runs, rows below cutoff removed, counter emitted. Gate 1 found F3 (the skipped-pass path logged a false sp_releaseapplock Warning and cleared the pool every pass) → fixed 984e069f (release only when acquired); gate 2 re-gate PASS — applock held from an outside session for 80 s: only the Information "purge skipped" line, no Warning, no pool clear; purging resumed when released.

Follow-up filed: cancel-between-batches release path not independently proven.

Implemented in `b3db64af`, live-gate fix `984e069f` (both merged to master). - Opt-in `ServerHistorian:SqlServer:RetentionDays` (null = keep forever) + `PurgeInterval` (1 h) + `PurgeBatchSize` (4000). - `HistorianRetentionService` purges `hist.Sample` per tag in `DELETE TOP(n)` batches on the clustered key and `hist.Event` by time; cutoff on source time. Primary-gated on `IHistorizationRoleView`; one purger per database via `sp_getapplock` (timeout 0 — a loser skips the pass). Metric `otopcua.historian.retention.purged{table}`. Optional `IHistorianBackendProvider.CreateRetention` (default null; QuestDB keeps its TTL). Since #668 it also purges `hist.SampleModified`. - Pooling trap handled: a session-owned applock survives `sp_reset_connection`, so a failed release clears the pool. **Live gates (docker-dev):** gate 1 — purge runs, rows below cutoff removed, counter emitted. Gate 1 found F3 (the skipped-pass path logged a false `sp_releaseapplock` Warning and cleared the pool every pass) → fixed `984e069f` (release only when acquired); gate 2 re-gate PASS — applock held from an outside session for 80 s: only the Information "purge skipped" line, no Warning, no pool clear; purging resumed when released. Follow-up filed: cancel-between-batches release path not independently proven.
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
auto-managed
queue management
 cross-driver
queue management
 driver/abcip
queue management
 driver/ablegacy
queue management
 driver/focas
queue management
 driver/mqtt
MQTT / Sparkplug B broker-subscribe driver
 driver/opcuaclient
queue management
 driver/s7
queue management
 driver/twincat
queue management
 phase/1
queue management
 phase/2
queue management
 phase/3
queue management
 phase/4
queue management
 phase/5
queue management
 phase/6
queue management
 queue/blocked
queue management
 queue/done
queue management
 queue/failed
queue management
 queue/in-progress
queue management
 queue/queued
queue management
No Label
Milestone
No items
No Milestone
Projects
Clear projects
No project
Assignees
Clear assignees
dohertj2 (dohertj2)
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: dohertj2/lmxopcua#667
Delete Branch "%!s()"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?