Database Administrator Interview Questions

"I started as a support engineer, and most of the tickets I ended up owning were slow reports and failed jobs, which kept pulling me into the database. My team lead let me take over the nightly backup checks, and within a year I was doing SQL Server maintenance for the whole team. For the last four years I've been a DBA looking after around forty SQL Server instances, including two availability groups, plus about a dozen PostgreSQL databases that came in with a newer product. I'm deepest on SQL Server. On PostgreSQL I'm solid on backups, replication and tuning, and still learning some of the internals. What keeps me here is that the database is where problems get real, and I like being the person who can say calmly what happened and how we get it back."

"A developer who writes good SQL cares whether their query is correct and fast. A production DBA owns everything around that. First, can we get the data back: backups that actually restore, within the time the business agreed. Second, is it up and fast for everyone at once: replication, failover, monitoring, capacity, and tuning that comes from seeing the whole workload rather than one query. Third, is it safe: who has access, what gets audited, and how changes and upgrades reach production without surprises. The short version I use is that developers own what the data means, and the DBA owns keeping it safe, available and recoverable. Good teams overlap a lot, but when something breaks at night, the DBA is the one expected to know the restore path."

"I'm interested because the role covers SQL Server and PostgreSQL at real scale, and from the description you're moving more of it to managed cloud services, which is where I want to grow. Before my first on-call shift I'd want to know four things. When was each important database last restored as a test, and how long did it take? How is failover set up, and has anyone actually run it recently? What does monitoring alert on, and which alerts are known noise? And what are the three things that break most often? In the first few weeks I'd mostly read runbooks, shadow someone on call and build my own map of the servers before changing anything, because a new DBA making confident changes in week one is how outages start."

"A full backup copies the whole database and is the base of every restore. A differential captures everything that changed since the last full, so it grows through the week, and at restore time you only need the most recent one. A log backup captures the transaction log since the previous log backup, and that unbroken chain is what lets you recover to a precise moment. For a busy database I'd typically run a weekly full, a nightly differential and log backups every few minutes, then tune that to the agreed data-loss limit. The restore path is the full, then the latest differential, then every log backup after it, in order. Engines name these differently, like RMAN incrementals in Oracle or a base backup plus archived WAL in PostgreSQL, but the idea is the same."

"RPO, the recovery point objective, is how much data we can lose, measured in time. If it's fifteen minutes, backups or replication have to capture changes at least that often. RTO, the recovery time objective, is how long we can be down. They push the design in different directions. A tight RPO means frequent log backups, and if it's close to zero, synchronous replication so every commit exists in two places. A tight RTO means you can't rely on restoring a big backup, because that can take hours, so you need a warm standby and a failover you've actually practised. I always get both numbers agreed in writing for each system, because a reporting database and the order system rarely need the same answer, and the cost difference between them is large."

"At my last company every backup job showed green, so everyone assumed we were covered. When I joined I set up a monthly test restore of our largest database onto a spare server, and the first one failed. The full backups were fine, but the log backups went to a share that an old cleanup script also swept, so every weekend we had gaps in the log chain. Recovering to a point on a Saturday would have been impossible. I fixed the script that day and moved log backups to their own location. The lasting change was automating it: every week a job restores the latest backups to a test server, runs a consistency check and reports how long it took. Now when the business asks how long a restore takes, we give them a measured number, not a guess."

"First I make sure nothing is still running, like a job repeating the delete, and pin down exactly what was run and when. I would not restore over production, because twenty minutes of other orders would be lost. I take a log backup of production straight away, so the backup chain covers the moment of the delete. Then I restore the last full backup and the log backups to a separate database, stopping just before the delete. I compare that copy with production and insert the missing customer rows back, watching for identity values, triggers, foreign keys and rows the application has changed since. I keep the business updated on how long it will take. In Oracle, if it's recent enough, flashback query might return the rows without any restore, and in PostgreSQL I'd do the same point-in-time restore to a separate instance. Afterwards I'd look at why a developer could delete directly in production."

"High availability is about surviving a single failure, like a server dying, with only a short blip, usually by failing over to a replica in the same site or a nearby zone. Disaster recovery is about losing the whole site or region and still coming back, from a copy somewhere far away. Replication mode is where the trade-off shows. Synchronous replication makes the commit wait until the replica has the change, so failover loses nothing, but every write pays the round trip, which is fine nearby and painful over long distances. Asynchronous replication doesn't wait, so writes stay fast, but on failover you can lose whatever hadn't shipped yet. So a common design is synchronous to a nearby replica for HA and asynchronous to a distant one for DR. And neither replaces backups, because a bad delete replicates instantly."

"First I split the lag in two: is the log reaching the replica late, or arriving on time and being applied late? In PostgreSQL, pg_stat_replication on the primary shows write, flush and replay lag separately, and for SQL Server availability groups the replica state views show the send queue and the redo queue. If sending is behind, I look at the network and at what the primary was doing, because a big batch update or an index rebuild can produce a flood of log in minutes. If applying is behind, I check the replica's storage, and whether long reporting queries on the replica conflict with replay. In PostgreSQL a hot standby can hold replay back for conflicting queries up to a configured delay. Then I line the lag graph up with the primary's workload, because lag that spikes at the same hour every night usually has a scheduled job behind it."

"I'd spend a minute or two confirming it's really down, from more than one place, because failing over on a network blip does more harm than good. If it is down, I'd estimate how quickly it could come back. If a restart would take a few minutes and our targets allow that, waiting keeps every transaction. If it's a hardware failure or could take an hour, failing over is usually right, but with a few seconds of lag some recent transactions may be lost. That's a business call as much as a technical one, so I'd say it plainly to the incident lead and follow what our runbook and data-loss target already say. Before promoting the replica I'd make sure the old primary is fenced off, so it can't come back, accept writes and leave us with two diverging copies. Later I'd try to recover the missing transactions from the old primary."

"I measure first rather than rebuilding on a timer. In SQL Server I look at fragmentation and page density per index, and I skip small indexes entirely, because a few hundred pages won't matter. The old rule of thumb is to reorganize when an index is lightly fragmented and rebuild when it's heavily fragmented. A reorganize is always online and can be stopped, but it doesn't update statistics. A rebuild recreates the index, refreshes that index's statistics with a full scan and compacts the pages, but it writes a lot of log and can block unless you use an online rebuild where the edition supports it. On modern SSD storage fragmentation matters less than it used to, and I've seen plenty of slow queries fixed by the statistics update that came with a rebuild, with the rebuild getting the credit. In PostgreSQL the same worry shows up as bloat, handled with vacuum and REINDEX CONCURRENTLY."

"That gap is the real problem, not just a detail. The optimizer built the plan around one row, so it probably chose a nested loop with lots of lookups and gave the query too little memory, which makes sorts and hashes spill to disk. Next I work out where the estimate went wrong. I check when statistics on those columns were last updated and how many rows have changed since, because freshly loaded data is the classic cause. I look for a function or an implicit type conversion on the filtered column, which hides it from the statistics. I check whether the plan was compiled for a different parameter value, and whether a table variable is involved. Then I fix the cause, rerun, and compare estimated and actual rows again, rather than jumping to a hint that forces a join type."

"When a procedure is first compiled, SQL Server looks at the parameter values it was called with, builds a plan for those, caches it and reuses it for every later call. That's usually good, but if the data is skewed, say most customers have fifty orders and one has five million, whichever value came first decides the plan for everyone. So the big customer might get nested loops meant for fifty rows. The fix depends on how often it runs. If it isn't called often, a recompile hint on each run is simple and reliable. If it's hot, I might optimize for a typical value, send the very large customers down their own code path, or use Query Store to force a plan that's good enough for both. Oracle and PostgreSQL have the same issue under different names, bind peeking and generic plans."

"We had a nightly batch that went from forty minutes to over three hours within a couple of weeks. Everyone assumed an index was missing, but the wait stats showed most of the time was spent waiting on memory grants and tempdb, not reading tables. The plans showed big sorts spilling to disk because the row estimates were far too low. The cause was a new client whose data skewed one large table, and automatic statistics updates hadn't kicked in yet because the change was below their threshold. I added a statistics update with a larger sample straight after the load step, and split one huge query into two stages with a temp table so the second part got accurate estimates. The batch went back to about thirty-five minutes, and I added an alert if it ever runs twice its usual time."

"I'd look at what's using the CPU right now, the top queries by CPU over the last few minutes, and compare them with the same hour yesterday. Query Store in SQL Server and AWR in Oracle keep that history, and in PostgreSQL I compare snapshots of pg_stat_statements. Usually it's one of two things. Either a query suddenly got a worse plan, which shows up as a new plan with far more CPU per execution, often after an overnight statistics update. Or a normal query is suddenly running far more often, like a new report or an integration calling one endpoint in a loop. For a plan regression, forcing the previous good plan, where the engine allows it, is a quick, reversible fix while I find the real cause. For a volume change, I'd track down the caller with the app team. I'd avoid restarting the server, because that throws away the cache and the evidence."

"First I'd look at the job history and see which steps take the time. Very often it's rebuilding every index on a few huge tables whether they need it or not. So I'd switch to measured maintenance: skip indexes below a size threshold, reorganize or rebuild only the ones that are actually fragmented and actually used, and update statistics only on tables where enough rows changed. Next I'd make the heavy work gentler, with online and resumable rebuilds where our edition supports them, so a rebuild can pause at a set time and carry on the next night. I'd spread the biggest tables across the week instead of one window. I'd add a hard stop, so anything unfinished by the cutoff waits for the next night. And I'd check whether backups or consistency checks are fighting for the same window."

"Blocking is ordinary lock waiting: session B wants a row that session A has locked, so B waits until A commits or rolls back, and that can go on for a long time. A deadlock is a cycle, A waits for B and B waits for A, and the engine spots it quickly and rolls one of them back with an error, so a deadlock on its own doesn't cause a long hang. For a hang I'm looking for a blocking chain. In SQL Server I check the blocking session ID in the requests view, in PostgreSQL pg_blocking_pids, and in Oracle the blocking session column. I follow the chain to the session that's blocking others but isn't waiting on anything itself. Often it's idle inside an open transaction, like an app that began one and never committed. I find out who owns it before I kill it, because killing it rolls back its work."

"First I get the evidence. SQL Server's built-in system health session keeps recent deadlock graphs, PostgreSQL writes the details to its log, and Oracle writes a trace file. The graph shows both statements, which rows or index keys each one held, and which each one wanted. Usually it's one of three things. The jobs touch the same tables in opposite orders, so I'd ask the developers to update in one consistent order. Or a missing index forces a scan that locks far more rows than needed, and a targeted index shrinks the overlap. Or each job does a whole night's work in one long transaction, and committing in batches fixes it. I'd also make sure both jobs retry on a deadlock error, because you can't rule deadlocks out completely. And I'd bring the graph to the developers, because it turns an argument into a shared puzzle."

"I'd start by making sure every application and every person has their own identity, ideally tied to the company directory, so nobody shares an admin login and every action can be traced. Then I grant permissions to roles, not individuals. The application gets a role that can read and write its own schema, ideally just run its procedures, but it doesn't own objects and can't change the schema. Migrations run under a separate deployment identity. Support gets read-only access, and where there's personal data, views that mask the sensitive columns. Developers get full rights in dev and test, and in production usually read-only, with higher access granted for a limited time and logged when they're working an incident. Finally I turn on auditing for logins and sensitive tables, and review who's in each role every quarter, because permissions pile up quietly."

"I'd start by asking what they couldn't do in the last few incidents, because the request usually hides a real problem, like waiting an hour for a DBA to kill a session. Then I'd explain why permanent admin is a no from me, and that it isn't about trusting them: every standing admin account is a risk if it's ever compromised, mistakes on production get much easier, and our audits expect access to be limited and traceable. What I'd offer is a break-glass path: during an incident they can request elevated access that's approved within minutes, expires after a few hours and is fully logged. I'd also give them read access to the monitoring views so they can diagnose without admin rights, and write runbooks for the common fixes. If the fast path is genuinely fast, people stop asking for permanent access."

"I split it into two lists. Things that should page someone are the ones where waiting until morning makes it worse: the instance down or refusing connections, backups or log backups failing past the window we promised, replication broken or lagging beyond our data-loss target, data or log storage about to fill, and blocking that has lasted more than a few minutes. Everything else goes to dashboards and a daily review: CPU, memory pressure, top waits, the most expensive queries, connection counts and growth. The key for both is a baseline, because high CPU on a reporting server at month-end is normal and the same number on a quiet Tuesday morning isn't. And every page should link to a runbook, because an alert nobody knows how to act on just trains people to ignore it."

"I'd start with history. Most monitoring keeps data file and backup sizes over time, and I'd break growth down by table, because usually a few tables, like audit logs or events, drive most of it. For CPU, memory and I/O I'd look at peaks and busy periods, not averages, since averages hide the month-end crunch. Then I project each trend forward, add headroom, and ask the business about launches or big new customers that would bend the line. That gives me a rough date for each resource. Before buying anything I'd look at cheaper levers: archiving old data, a retention policy for logs, compression, and tuning the handful of queries that use most of the CPU. Whatever's left becomes a plan with dates, worked back from how long it takes to get hardware or move to a bigger cloud tier, so we act months early rather than during an outage."

"The worst one was our order database refusing new orders on a Friday evening because its data drive filled up. A reporting team had started a huge export into a staging table in the same database. I first confirmed it was storage and not corruption, told the incident channel what I knew and that I'd update every fifteen minutes, then stopped the export after checking with its owner, dropped the staging table and extended the volume. Orders were flowing again in about forty minutes, with no data lost. In the review we agreed three changes: storage alerts at two levels instead of one very late one, a separate database for reporting scratch work, and a growth forecast for every production volume. That earlier alert has caught two near misses since."

"Early on I ran a cleanup script on what I thought was the test server, and it was production. I had two connection windows open with almost the same names. It deleted old session rows, so nothing critical, but a couple of hundred active users were logged out. I told my manager straight away, and because it was only session data we didn't need a restore, just a note in the status channel. What I changed was how I work. My production connections now show in a different colour in my tool, every manual data change starts inside a transaction and I check the row count before committing, and anything larger than a handful of rows becomes a reviewed script instead of something I type live. That row count check has stopped me twice since."

"First I check how much space is left and how fast it's filling, because that tells me how long I have. If it's minutes, I add a second log file on another drive to buy time. Then I ask SQL Server why the log can't be reused, which sys.databases shows as the log reuse wait. If it's waiting on a log backup, the backup job has probably failed, so I run one and fix the job. If it's an active transaction, I find the oldest open one and its owner before deciding whether to kill it. If it's an availability replica, a secondary is behind or disconnected and the primary keeps log for it. If it's replication, the log reader or change capture has stalled. What I won't do is switch to simple recovery, which breaks the log chain, or shrink the file blindly, which fixes nothing. PostgreSQL has the same pattern when a failing archive command or an abandoned replication slot holds WAL."

"I treat it as a project. First I read the release notes for removed or changed features, check the application's drivers, and record baseline timings for the important queries. For the method, in place is simpler, but the outage lasts as long as the upgrade. I usually prefer building the new version alongside the old one and keeping it in sync, with logical replication in PostgreSQL or backups and log shipping in SQL Server, so the cutover is a short switch. I rehearse the whole thing on a production copy and time every step. For rollback, you usually can't downgrade a database in place once it's upgraded, so my back-out is to leave the old server untouched until we're confident. In SQL Server I'd also keep the old compatibility level at first and raise it after testing, so optimizer changes don't surprise us on day one."

"I led moving a PostgreSQL database of around two terabytes from our own servers to a managed cloud instance. We were allowed thirty minutes of downtime, so a dump and restore was out. I used logical replication to keep the cloud copy in sync for two weeks, and we rehearsed the cutover three times on a copy. The rollback plan was that the old server stayed untouched, and for the first two hours after cutover we replicated changes back to it, so backing out wouldn't lose new orders. We had a written go or no-go checklist with row counts, sequence values and timings for key queries. The one surprise in rehearsal was sequences: logical replication on the version we ran copied the rows but not the sequence values, so we scripted setting each one past its highest key just before cutover. On the night we didn't need the rollback, and the cutover took eighteen minutes."

"A team wanted to add a non-nullable column with a default to our largest table in a normal daytime release. On the engine version we ran, that meant rewriting the whole table under a lock, which I estimated at close to an hour of blocked orders. Instead of just saying no, I ran the change on a restored copy of production and showed them the timing and the lock it held. Then I proposed a different order: add the column as nullable, backfill it in small batches overnight, then add the constraint once the data was in. They were a bit frustrated at first because it spread the change across three releases, but it went out with no impact at all. Afterwards the team lead asked me to look at their migrations earlier, which is really what I wanted."

"I write the runbook for someone who's tired and has never done it before, so it has the exact commands, where the backups live, which account to use and roughly how long each step should take, so they can tell when something is off. But a document nobody has followed isn't trustworthy, so during our scheduled restore tests I ask a teammate to run the restore from the runbook while I watch and keep quiet. Every place they hesitate becomes an edit. I also rotate who runs the drill, so I'm never the only person who has done it recently. After any incident or change to backups or servers, updating the runbook is part of closing the ticket. The goal is that me being on holiday is never a risk to the business."

"Most gatekeeping comes from getting involved too late, when a change is ready to ship and the only thing left to say is no. So I try to be in design conversations early, when a better index or table shape costs nothing. I also write down our rules, like how to add a column to a big table or when a migration needs review, so teams aren't guessing what will get stopped. I give developers read access to query performance data in production, so they can spot their own slow queries before I do. And when I do push back, I bring an alternative and help make it work. When teams start messaging me before they write the migration, not after, I know it's working."