A database that nobody is watching announces its problems the hard way -- through a support ticket, a stalled batch job, or an outage in the middle of the night. Monitoring and reporting turn a database from a black box into a system administrators can act on before small problems become production incidents, and understanding what to watch (and why) is core to keeping any data system healthy.
Building a Monitoring Baseline
Effective monitoring starts with knowing what "normal" looks like. Administrators track storage growth trends so they can forecast when a volume will fill up weeks or months in advance, rather than discovering it when writes start failing. Throughput (queries or transactions per second) and resource utilization -- CPU, memory, and disk I/O -- are tracked together because a spike in one often explains a dip in another: high CPU combined with falling throughput usually points to inefficient queries or a missing index, while high disk I/O with normal CPU often points to memory pressure forcing the engine to read from disk instead of cache. OS-level performance metrics matter just as much as database-internal metrics, because the database engine is still a process competing for resources with the operating system and every other service on the host.
Alerts That Actually Get Acted On
Raw metrics are only useful once they are turned into alerts and notifications with sensible thresholds. Storage, throughput, and utilization alerts warn of slow-building problems; backup-completion and job-completion alerts confirm that scheduled maintenance actually ran and finished successfully rather than silently failing overnight. A mature monitoring setup distinguishes between a warning (approaching a threshold) and a critical alert (threshold breached), and routes each to the right escalation path so administrators are not paged for every minor fluctuation.
Logs as the System's Memory
Transaction log files record every change made to the database and are essential for both recovery and auditing -- they are the mechanism that lets a database roll forward or roll back to a consistent state after a crash. System log files capture broader engine-level events: startup/shutdown, configuration changes, errors, and warnings. Reviewing these logs regularly (not just after an incident) surfaces recurring errors before they escalate.
Deadlocks and Connection Tracking
Deadlock monitoring watches for situations where two or more transactions each hold a lock the other needs, creating a standoff that the database engine must break by killing one transaction. Frequent deadlocks are a design smell -- usually inconsistent lock ordering in application code -- and monitoring surfaces the pattern so developers can fix it at the source. Tracking connections and sessions, including failed connection attempts, serves double duty: it reveals capacity problems (too many open sessions exhausting a connection pool) and security problems (a burst of failed logins can indicate a brute-force attempt or a misconfigured application retrying with bad credentials).
Key Mechanics
- Storage growth, throughput, and CPU/memory/disk utilization alerts exist to catch slow-building problems before they cause outages.
- Backup-completion and job-completion alerts confirm scheduled maintenance actually succeeded, not just that it started.
- Transaction logs support recovery and rollback; system logs capture engine-level events like startup, shutdown, and errors.
- Deadlock monitoring identifies competing transactions holding conflicting locks, which the engine resolves by terminating one.
- Tracking failed connection attempts serves both capacity planning and security monitoring purposes simultaneously.
Exam Tip: Don't confuse a transaction log with a system/error log -- the transaction log exists for data recovery and rollback, while the system log records engine-level events like startup, configuration changes, and errors. A scenario about restoring to a consistent point in time points to the transaction log.
Exam Tip: A scenario describing recurring deadlocks is testing whether you recognize it as an application/query design issue (inconsistent lock ordering), not a hardware or storage problem -- the fix is in the code, not the infrastructure.
Exam Tip: When a scenario mentions a sudden spike in failed connection attempts, the exam is testing whether you connect monitoring to security, not just performance -- this is often the first sign of a credential-stuffing or brute-force attack.
Diagram
Worked example: A DBA notices a nightly backup-completion alert has stopped firing for three consecutive nights, while storage-growth monitoring shows the backup volume is no longer increasing in size. Cross-referencing the system log reveals the backup job is failing silently due to a permissions change on the target share. Because the monitoring stack tied a specific alert to job completion rather than just job start, the gap was caught after two missed nights instead of being discovered a month later when a restore was actually needed.