SQL Server / Advanced / Lesson 17

Operations, integrity, and observability

Build a SQL Server operating routine that detects threats before users discover them. Connect capacity, backup age, integrity checks, jobs, waits, and controlled maintenance.

About 5 min readContent reviewed 2026-10-23

On this page

Lesson goals

Choose actionable health indicators with owners.
Explain CHECKDB and safe corruption response.
Use interval evidence for capacity and wait investigation.

Monitor outcomes and recovery readiness

Operational health includes successful business requests, latency, error rate, backup freshness, restore readiness, integrity-check recency, storage headroom, and successful required jobs. A running SQL Server service can still have an unavailable database or a broken order path. Pair infrastructure metrics with a narrowly scoped application health check that does not mutate business data.

Every alert needs an owner, threshold rationale, and next diagnostic step. A disk alert should arrive early enough to retrieve backups or expand capacity through the approved process. Repeated noisy alerts teach operators to ignore real incidents. Use trends and expected workload cycles, and distinguish a warning requiring review from an outage requiring immediate escalation.

Observe data, log, and tempdb separately

Data files, transaction logs, and tempdb have different growth causes. A long transaction can delay log reuse; a missing log-backup process can cause FULL-recovery log growth; large sorts, hashes, temporary tables, or version retention can pressure tempdb. File size alone does not explain current used space or why reuse is blocked. Inspect the relevant resource before changing growth settings.

The query reports current-database log usage without clearing counters or shrinking files. A percentage must be paired with absolute size and growth trend. Ten percent free on a tiny log may be insufficient for a normal batch. Repeated shrink-and-grow cycles add operational cost and do not repair workload or backup design. Plan capacity and sensible growth increments instead.

sql Inspect transaction-log space

SELECT total_log_size_in_bytes/1048576.0 AS total_log_mb,
 used_log_space_in_bytes/1048576.0 AS used_log_mb,
 used_log_space_in_percent
FROM sys.dm_db_log_space_usage;

The DMV reports the current database. Access requires documented state permissions, which differ across versions.

Detect corruption before recovery is urgent

DBCC CHECKDB checks database consistency through several logical and physical validations. Schedule integrity checking with an appropriate resource budget and retain results. PHYSICAL_ONLY is a limited check and should not be described as equivalent to the full logical validation. A backup's recent timestamp is no assurance that the stored database is healthy.

The conceptual command includes no repair option. If corruption is reported, preserve evidence, investigate storage and system errors, and work through a recovery plan using a known-good backup where possible. REPAIR_ALLOW_DATA_LOSS is an emergency last resort that can remove data, not a routine maintenance fix. Do not make destructive repair the learner's first response to an error.

sql A consistency check without repair

-- Resource-intensive inspection; schedule only in an authorized lab.
DBCC CHECKDB (N'TrainingShop') WITH NO_INFOMSGS,ALL_ERRORMSGS;

No clean bill of health is fabricated. Even a non-repair check consumes resources and requires an approved schedule.

Use waits as clues over a measured interval

Server wait statistics are cumulative since startup or the last reset and include benign background activity. Compare snapshots across the incident window instead of ranking lifetime totals alone. Lock waits, storage-related waits, log flush waits, and client-consumption waits suggest different hypotheses. A wait name is not a complete root-cause diagnosis or an instruction to change hardware.

Do not clear shared counters for convenience. Record startup time, interval length, workload, and counter resets. Correlate waits with active requests, query history, I/O, CPU, and application behavior. SQL Server 2022 requires VIEW SERVER PERFORMANCE STATE for several performance DMVs where earlier releases used VIEW SERVER STATE; follow the documentation for the exact view rather than granting sysadmin.

Maintain based on evidence and recoverability

Index and statistics maintenance should respond to workload needs, data change, resource budgets, and observed benefit. Rebuilding every index nightly can create substantial I/O, log generation, and interference without a useful gain. A fragmentation percentage alone does not justify every rebuild. Edition and version also constrain options such as online operations.

Validate job execution and notification paths, keep a tested rollback plan for configuration or compatibility changes, and stage patches with representative workload checks. Protect logs and monitoring access because query text may contain sensitive values. A useful runbook links a symptom to safe inspection, a decision owner, an approved intervention, and verification of business service and recovery readiness.

Common errors and fixes

The database is healthy because the service is running.

Why it happens: Process liveness was mistaken for business readiness.
How to fix it: Check database availability, request success, backups, and integrity evidence.
Prevent it next time: Define layered health checks.

Log shrinks but immediately grows again.

Why it happens: The reuse or workload cause remains.
How to fix it: Inspect usage, reuse waits, transactions, and log-backup age.
Prevent it next time: Plan capacity and repair the root process.

Repair was used before recovery assessment.

Why it happens: A destructive option was treated as ordinary maintenance.
How to fix it: Preserve evidence and evaluate known-good restore options first.
Prevent it next time: Keep corruption response in a reviewed runbook.

Check your understanding

1. Wait totals are high after a server has run for months. What comparison is useful?

Show worked solution

Take interval snapshots without resetting shared counters, account for restart/reset boundaries, and correlate deltas with workload and request metrics. Lifetime totals alone cannot locate today's regression.

2. A full integrity check is overdue but PHYSICAL_ONLY ran yesterday. Is the full requirement satisfied?