The free 5-point checklist
Five questions to ask about every data flow. If you cannot answer yes to all five, your dashboards partly rely on luck.
1. Every failure raises an alert
A failed job should tell someone, by e-mail, Teams or your monitoring, before a user spots a wrong dashboard. Check that every scheduled job notifies an operator on failure, and that Database Mail actually works.
2. Late and long-running jobs are caught
A load that finishes at 9 am instead of 5 am is often as bad as a failure. Compare each run with its usual duration and alert above a threshold (twice the average, say). Catch skipped runs too: a job that never ran does not fail, it just stays quiet.
3. Reruns are safe
A step that fails on a transient error (network, lock, timeout) should retry on its own, and a rerun must not create duplicates. Check the retry settings and that every load is idempotent (MERGE, or clear the slice before inserting).
4. Freshness and row counts are checked
A job can succeed and load zero rows, or yesterday's data. Add a simple check at the end of the chain: date of the latest row, row count against previous days, totals reconciled with the source. That check is what really protects decision makers.
5. Every flow has an owner and a runbook
When a flow breaks at 3 am, who is told and what do they do? Every job needs a named owner (and an owner login that still exists), a description and a short runbook: how to rerun, what to check, who to tell.
The free SQL Server Agent script
For flows that run in SQL Server Agent or SSIS, our T-SQL script checks the list in seconds against your msdb history: jobs that failed in the last 30 days, runs that took far longer than usual or are still running, scheduled runs that never happened, jobs with no alerting at all, steps without retry and jobs whose owner login no longer exists.
Download the script (samix-etl-job-health.sql)
- read-only: SELECTs on msdb only, no object created, no business data read;
- nothing is sent: you read the results in SSMS or sqlcmd;
- permissions: read access to msdb (db_datareader or sysadmin), SQL Server 2008 R2 and later.
For the overall health of the instance (backups, security, version, performance), try the free SQL Server health score too.
The one-day diagnosis: €800 excl. VAT
A senior DBA spends a day on your flows and tells you exactly what can break, what is already breaking quietly, and in which order to fix it.
- History. Failures, delays and skipped runs over recent weeks, with their causes.
- Alerting and monitoring. Who is told, how, and what slips through.
- Retries and idempotency. What happens when you rerun a half-finished load.
- Data quality. The missing freshness and volume checks, and where to put them.
- Owners and runbooks. Orphaned flows and the runbooks to write first.
Within 48 hours you get a written report and a prioritised fix list, then a one-hour video walkthrough. After that it is your call: apply the fixes yourselves, or have us do the work, up to modernising your data platform.
