Database Administrator Interview Questions & Answers (with Model Answers)

Model Answer

I start from the business requirements, defining the recovery point and recovery time objectives, then choose a mix of full, differential, and transaction-log backups to meet them. I store copies offsite or in another region and encrypt them. Crucially, I test restores regularly, because an untested backup is just a hope, not a strategy. I document the recovery runbook so anyone on call can execute it.

Model Answer

I capture the execution plan to see where time and rows are spent, looking for table scans, missing indexes, or bad cardinality estimates. I check wait statistics and whether the issue is CPU, I/O, or locking. Then I test targeted fixes such as a covering index, rewritten predicate, or updated statistics, measuring before and after. I avoid blanket changes and validate that the fix does not regress other queries.

Model Answer

I use replication or clustering technologies such as Always On availability groups, streaming replication, or a managed multi-AZ setup with automatic failover. I separate it from backups, since HA protects against node failure while backups protect against data corruption and human error. I test failover regularly and monitor replication lag. The architecture matches the agreed RTO rather than gold-plating beyond need.

Model Answer

A bad bulk update corrupted a key table without a transaction wrapper. I isolated the database, identified the last clean transaction-log point, and performed a point-in-time restore to a side instance to extract the correct rows. I reconciled them back into production with minimal downtime, then mandated transactions and staging for bulk operations. No data was permanently lost.

Model Answer

I apply least privilege with roles rather than individual grants, encrypt data at rest and in transit, and audit privileged access. I keep credentials out of application code using secrets management and rotate them. I separate duties so no single account can both change data and erase its audit trail. Regular access reviews catch privilege creep.

A clustered index defines the physical order of rows in the table, so there can be only one per table and it is typically on the primary key. A non-clustered index is a separate structure with pointers back to the rows, and you can have many per table. Clustered indexes speed range scans on the key while non-clustered indexes help lookups on other columns.

Atomicity means a transaction completes fully or not at all; Consistency means it moves the database from one valid state to another respecting constraints; Isolation means concurrent transactions do not interfere with each other's intermediate state; Durability means committed changes survive crashes. Together they guarantee reliable processing even under failure and concurrency.

Normalization organizes data to reduce redundancy and update anomalies, typically to third normal form, by splitting data into related tables. You denormalize deliberately for read-heavy workloads where joins are costly, accepting controlled redundancy to improve query performance. The trade-off is faster reads versus more complex writes and consistency management.

Locks enforce isolation by preventing conflicting concurrent access, but a deadlock occurs when two transactions each hold a lock the other needs. The engine detects this and kills one as the victim. To reduce deadlocks I keep transactions short, access objects in a consistent order, and use appropriate isolation levels, and I analyze deadlock graphs to fix the offending access pattern.

Synchronous replication confirms a commit only after the replica has written it, guaranteeing zero data loss but adding latency tied to the replica's distance. Asynchronous replication commits on the primary first and ships changes after, offering lower latency but risking some data loss on failover. The choice balances durability against performance and geography.

Situation: monitoring showed transaction-log files filling rapidly toward a full disk. Task: I had to act before the database stopped accepting writes. Action: I diagnosed a long-running uncommitted transaction blocking log truncation, worked with the developer to commit it safely, took a log backup, and added alerting at a lower threshold. Result: the database never went read-only and we avoided an outage that would have hit thousands of users.

Situation: a reporting query timed out and dragged down the whole instance. Task: I needed to restore performance without breaking other workloads. Action: I read the execution plan, added a covering index, updated stale statistics, and moved heavy reporting to a read replica. Result: the query went from timing out to running in seconds and overall instance load dropped substantially.

Situation: a storage failure took a production database offline at peak. Task: I had to restore service within the agreed RTO. Action: I failed over to the standby, validated data consistency, and communicated status updates every few minutes to stakeholders. Result: we were back online inside the target window, and the tested failover plan proved its worth.

Situation: a large table needed a new indexed column without downtime. Task: I had to apply it safely on a busy production database. Action: I used an online index operation, batched the backfill in small transactions during low-traffic hours, and monitored locking and replication lag throughout. Result: the change completed with no user-visible impact and no blocking incidents.