Database Administrator interview questions and answers

"A report that used to run in seconds now takes minutes. How do you investigate?"

Establish what changed. Ask when it started and whether anything was deployed, whether the data volume grew, and whether it is slow for everyone or one user. Then look at the query: its execution plan, whether it is scanning where it used to seek, and whether statistics are current.

Common causes are worth naming: a new leading wildcard or function on an indexed column, an index dropped or not maintained, stale statistics, parameter sniffing on a reused plan, or simply ten times the rows. Say that you would confirm the cause before changing anything in production and would test the fix on a copy first.

"How do you know your backups work?"

Answer with restores, not schedules. Describe a routine of test restores to a separate server, checking that the restored database opens, that row counts and a few business checks look right, and that the elapsed time fits the recovery window. Record each test.

Add the parts people forget. The backup files must sit somewhere the same failure cannot reach, and the log chain must be intact if point-in-time recovery is promised. Say too that someone other than you should be able to perform the restore from the documentation alone.

"Two applications keep deadlocking. What do you do?"

Explain what a deadlock is, briefly, then how you would find it: capture the deadlock information the engine produces, identify the two transactions and the resources each held and wanted. The fix usually lies in the application: access tables in a consistent order, keep transactions short, avoid user interaction inside a transaction, and review whether the isolation level is stricter than needed.

Mention that adding an index sometimes removes a deadlock by reducing the rows a statement locks.

"A manager asks for direct read access to the production database. How do you respond?"

Do not refuse flatly, and do not simply grant it. Ask what they actually need: a report, a number, or ongoing access. Offer the safest route that meets it, such as a view, a reporting replica or a scheduled extract, rather than a login against production. If direct access is genuinely required, grant the minimum privilege on the minimum scope, time-limited and recorded.

Panels are checking that you protect the data and the platform without becoming an obstacle to the business.

"A database is reported corrupt. Walk us through your first hour."

Stop making it worse: take the system out of use if that is safe, and do not restart repeatedly hoping it clears. Establish the scope by checking the engine's integrity checks and error logs, and find out when the corruption appeared, because it decides which backup is clean. Preserve the current files before attempting anything.

Then choose between repair and restore based on the recovery objectives, and involve the vendor or a senior colleague where the platform supports that. Communicate a realistic estimate to the business early, and record every step you take, because the post-incident review depends on it.

"How do you handle schema changes on a live system?"

Describe a process: review the change, test it on a copy with production-sized data, plan how long it will lock tables, prepare a rollback, schedule it in a maintenance window, and take a backup immediately before. For large tables, mention approaches that avoid a long lock, such as adding a nullable column rather than one with a default, or making the change in stages.