Automated Updates and Scheduling

Scope

Operational scheduling patterns (daily, weekly, monthly) for connector maintenance, data freshness, housekeeping and resilience across SQL Agent, alternative schedulers and lightweight runners.

Objectives

  • Keep class tables & metadata current.

  • Enforce retention (logs, audit, batch history).

  • Detect drift (assembly versions, metadata changes, excessive sync errors).

  • Provide predictable windows for heavy operations (batch updates / value list refreshes).

Core Scheduled Procedures Daily ~~~~~ - spMFUpdateAllncludedInAppTables (incremental class table sync) - spMFCheckAndUpdateAssemblyVersion (ensure CLR assembly matches installed M-Files version) - spMFGetMfilesLog (optional export for external SIEM) - spMFUpdateObjectChangeHistory (if not piggybacking on other routines)

Weekly

  • spMFDropAndUpdateMetadata (idempotent; only acts when structural change exists)

  • spMFClassTableStats (reporting mode for trend snapshots)

Monthly

  • spMFDeleteHistory (apply retention policy)

  • spMFSynchronizeValueListItemsToMFiles (if mastering some lists externally)

Event / Threshold Triggered

  • spMFUpdateSynchronizeError (after detecting syncError > 0)

  • Custom escalation job (objects with >3 conflicts / 24h) (Planned)

Scheduling Matrix (Initial)

Frequency vs Procedure (baseline recommendation)

Procedure

Daily

Weekly

Monthly

Conditional

spMFUpdateAllncludedInAppTables

Yes

spMFCheckAndUpdateAssemblyVersion

Yes

Post M-Files upgrade

spMFDropAndUpdateMetadata

Yes

Structural change detected

spMFDeleteHistory

Yes

spMFGetMfilesLog

Optional

After major incident

spMFUpdateSynchronizeError

On syncError

spMFSynchronizeValueListItemsToMFiles

Optional

On staged list changes

spMFUpdateObjectChangeHistory

Yes (unless embedded)

Example: Daily Update Job (Incremental)

-- Step 1: Incremental class table updates
EXEC dbo.spMFUpdateAllncludedInAppTables @Debug = 0;

-- Step 2: Change history capture (if separated)
EXEC dbo.spMFUpdateObjectChangeHistory @Debug = 0;

-- Step 3: Sync error remediation
EXEC dbo.spMFClassTableStats @ClassTableName = NULL, @IncludeOutput = 1; -- gather all
IF (SELECT SUM(syncError) FROM ##spMFClassTableStats) > 0
  EXEC dbo.spMFUpdateSynchronizeError @TableName = NULL; -- iterate inside proc (future enhancement)

-- Step 4: Assembly version safeguard
EXEC dbo.spMFCheckAndUpdateAssemblyVersion;

SQL Agent Template Notes

  • Separate long-running metadata operations into their own schedule window.

  • Configure failure notifications (Database Mail) for any non-zero severity output logged to MFLog.

  • Stagger heavy jobs across off-peak hours to avoid vault API saturation.

Alternative Schedulers

  • PowerShell runner (see Addons & Automation Tools) for SQL Express environments.

  • Windows Task Scheduler invoking sqlcmd / PowerShell scripts.

  • Cross-platform orchestrators (GitHub Actions self-hosted, Azure Automation) – future examples.

Retention & Compliance

Invoke spMFDeleteHistory with a retention boundary (e.g. 90 days) consistent with policy; validate counts via MFvwLogTableStats before and after initial run.

Monitoring Integration

  • Persist job outcome + duration to a custom table (Planned snippet).

  • Feed summary metrics (conflict count, rows updated, log purged) to centralized dashboard.

Planned Enhancements / TODO

  • Add PowerShell script examples (Invoke-Sqlcmd with retry & logging).

  • Provide YAML manifest templates for non-SQL Agent schedulers.

  • Add consolidated reporting procedure (aggregates key stats into one result set).

  • Introduce escalation job script skeleton.

  • Include performance benchmark ranges for typical vault sizes.

Migration Note

Content migrated from legacy page: Daily and monthly routines for agents.