Database Management and Maintenance

DS0-002 · 54 questions

• A database data file grows by roughly 40 GB per month, and the volume holding it has 50 GB of free space left. Today the team learns about full disks only when applications begin to fail. What should the DBA configure so the team is warned with time to act?
• A nightly ETL job that loads the reporting database stopped working three days ago. The scheduler sends an email only when the job succeeds, and nobody noticed that the success emails had stopped. Which monitoring change addresses the root problem?
• During an audit, a DBA discovers that no database backups have run for a week. After a patching session, the scheduler service that triggers backups was left disabled, so no backup job started and no errors were logged. Which alerting approach would have caught this?
• A DBA must pick a weekly maintenance window and wants to understand how the database is normally used on weekdays compared with weekends. Which approach gives the most useful information?
• Users report that an order-entry database seems slower than usual this week. The DBA collects current metrics but has no record of what CPU, wait times, or query response times looked like when users considered performance normal. Which practice would have made this complaint quick to evaluate?
• Over several months, administrators made manual changes on individual database servers, and the servers no longer behave consistently. Memory caps, maximum connections and logging settings now differ from the approved build standard. What should the DBA do?
• A capacity planning report must state how much work the database completes each second during the evening peak so that it can be compared with the expected growth in orders. Which metric should the DBA report?
• The database service on a production server restarted unexpectedly three times overnight. Users noticed brief outages, but no planned work was scheduled. What should the DBA do first to find the cause?
• A database server responds slowly. Monitoring shows moderate CPU use, but the operating system reports a high rate of paging to the page file or swap space, and available physical memory is near zero. What is the most likely problem?
• CPU utilization on a database server has stayed near 100 percent since a new sales report was deployed on Monday. Before Monday, CPU averaged 35 percent. What should the DBA do first?
• After a database doubled in size over six months, the DBA notices the buffer cache hit ratio has fallen steadily and physical disk reads have risen, though the query mix and the server's RAM are unchanged. What is the most likely explanation?
• Monitoring on a database server shows CPU at about 15 percent, ample free memory, and most query wait time attributed to reading and writing data files. Disk queue length is high and average read latency has climbed sharply during business hours. What is the bottleneck?
• A large overnight batch loads millions of rows in one transaction. Partway through, the volume holding the transaction log runs out of space and the batch fails. Monitoring covered only the data file volume. Which change best prevents a recurrence?
• A cloud-hosted database is billed on consumption, including I/O requests, storage and data transferred out. After a partner integration went live, the monthly bill rose sharply although the user count stayed flat. Which practice will show what is driving the increase?
• An order application intermittently receives errors stating that its transaction was chosen as a deadlock victim and rolled back. The errors happen a few times a day under normal load, and retrying usually succeeds. What should the DBA do to address the cause?
• A reporting session has been waiting for four minutes on a row that another session updated but has not yet committed. No error has been raised, and the waiting session resumes as soon as the other session commits. A colleague calls this a deadlock. Which statement is accurate?
• At peak times an application starts receiving errors that new database sessions cannot be opened because the server has reached its connection limit. Off-peak, everything works. Several application servers each run their own connection pool. What should the DBA monitor and adjust?
• Overnight monitoring shows several thousand failed login attempts against a privileged database account, all from a single external IP address within two hours. No successful logins from that address are recorded. How should the DBA treat this event?
• A database vendor releases a security patch that fixes a remotely exploitable vulnerability in the engine version running in production. The organization has development, test and production environments and a monthly maintenance window. What is the best approach?
• A production database is four cumulative patches behind the vendor's current release. The team has skipped patches for a year because an earlier patch once broke an application. Which change to the team's practice is most appropriate?
• A storage controller on a database server logged hardware errors overnight before being replaced. The database is online and users report no problems, but the DBA is concerned that some data pages may have been written incorrectly. What should the DBA do?
• A team keeps database backups for seven days and runs a full database integrity check once a month. An auditor says this schedule could leave the team unable to recover a clean copy. What is the auditor's concern?
• During the weekly review of database logs, a DBA notices dozens of automatic file growth events on the same data file over the past month, each occurring during business hours. No outage has occurred yet. What is the best response?
• After a large purge removed about half the rows from an orders table, range queries on its indexes became slower. Index statistics show high fragmentation and low page density on several indexes. What should the DBA do?
• A heavily written table has eleven indexes, and insert latency has grown as indexes were added over the years. Index usage statistics collected over a full business cycle show that four indexes are never used for reads and one duplicates another's key columns. What should the DBA do?
• A query that runs thousands of times a day filters a 40-million-row customer table with WHERE emailaddress = ?. The execution plan shows a full table scan, and no index exists on emailaddress. What is the most effective fix?
• On a table with an index on orderdate, a column of type TIMESTAMP, this query performs a full scan: SELECT orderid FROM orders WHERE EXTRACT(YEAR FROM orderdate) = 2025; Which rewritten WHERE clause returns every 2025 order and allows the index to be used?
• Every month-end, transaction volume on a finance database roughly triples for three days, and response times degrade badly during that period. For the rest of the month, performance is fine. What should the DBA do?
• A transaction history table holds ten years of data, and policy requires keeping only the most recent seven years. The monthly DELETE that removes expired rows runs for hours, generates heavy logging and blocks other work. What design change best addresses this?
• A customer table includes several large, rarely used columns, such as a profile photo and free-text notes, alongside small columns that are read constantly. The DBA moves the large columns into a separate table that shares the customer primary key. Which technique is this?
• Developers have been running schema changes directly against the production database at midday, causing lock waits and two brief outages this quarter. Management asks the DBA for a process fix. What should the DBA implement?
• A development team deploys application code through an automated CI/CD pipeline, but database schema changes are still applied by hand, and environments regularly drift apart. The team wants schema changes to move through the same pipeline. What should the DBA set up?
• During an outage, an on-call DBA added an index to a production table to relieve severe blocking. Performance recovered, but no change ticket was raised and the index exists only in production. What should happen next?
• The test database was copied from production a year ago. Recent releases passed testing but failed in production because of data patterns that did not exist in the old copy. The production data includes customer PII. What should the DBA do?
• Two DBAs edited the same stored procedure directly in production on the same day. The second save silently overwrote the first DBA's fix, and nobody could say which version had been running the previous week. What practice prevents this?
• A maintenance window on a production database will include four tasks: a full backup, rebuilding several fragmented indexes, updating optimizer statistics, and purging rows older than the retention period. Which task should run first?
• A DBA must correct a batch of customer rows where the city was entered as 'Pittsburg' instead of 'Pittsburgh' for addresses in Pennsylvania. The production table has millions of rows. Which approach is safest?
• A DBA is defining a customer table: CREATE TABLE customer (customerid INT PRIMARY KEY, email ...); Every customer must have an email address, and no two customers may share one. Which column definition enforces both rules in the table itself?
• A populated orders table with millions of rows needs a new status column. Business rules say that existing orders should be treated as 'NEW' and that the column must never contain NULL. Which statement adds the column correctly in one step?
• Analysts need to query employee names, departments and hire dates from an employee table that also contains salary. They must never see salary, the data they query must always be current, and the DBA does not want to duplicate the data in another table. What should the DBA do?
• The same complex query, joining orders, customers, products and regions with several filter rules, has been copied into fifteen different reports. When a business rule changed, five reports were missed and now show different totals. What should the DBA do?
• Queries on a person table often filter on lastname and firstname together, and many others filter on lastname alone; none filter on firstname alone. Names are not unique. The DBA wants a single index that supports both query patterns. Which statement should the DBA use?
• An Orders table is currently a heap with no indexes. Most queries retrieve every column for a range of order dates, and a smaller set of lookups finds orders by CustomerID. Which indexing design best supports the primary access path?
• After a nightly bulk load tripled the size of a Sales table, several reports that used to finish in seconds now take minutes. Execution plans show estimated row counts far lower than the actual rows returned. What should the DBA do first?
• A sales dashboard runs under the READ UNCOMMITTED isolation level and occasionally displays order totals that include rows from transactions that are later rolled back. Which change prevents this with the least additional blocking?
• A month-end report runs inside one transaction: it counts the open invoices, then sums their amounts. Under ANSI SQL isolation levels, new invoices inserted between the two steps make the count and sum disagree. Which isolation level prevents this, and at what cost?
• An order-entry database also serves heavy reporting queries. Under the current isolation level, long reports take shared locks that block order updates. Reports must still see only committed data. Which isolation approach best resolves the conflict?
• An audit finds rows in the Orders table whose CustomerID values do not exist in the Customers table, where CustomerID is the primary key. Which statement prevents new orphaned orders from being created?
• A Customers table is referenced by both an Addresses table and an Invoices table. Deleting a customer should automatically remove that customer's addresses, but a customer who has invoices must not be deletable. Which foreign key configuration meets both requirements?
• An Orders table stores CustomerName and CustomerAddress on every order row. When a customer moves, the update often misses some rows and reports show two different addresses for one customer. Which design change removes the cause of this anomaly?
• An Employees table has the columns EmployeeID (primary key), EmployeeName, DepartmentID and DepartmentName. DepartmentName is determined by DepartmentID. Every value is atomic and the key is a single column. Which normal form does the table violate?
• A KPI dashboard query joins eight normalized tables and takes too long to refresh. The underlying data is loaded once per night and the dashboard is read hundreds of times a day. Which approach best improves read performance for this workload?
• An OrderLines table stores Quantity, UnitPrice and LineTotal. Several applications write to it, and some forget to recalculate LineTotal when Quantity changes. The DBA wants LineTotal to always equal Quantity multiplied by UnitPrice without relying on application code. Which solution meets this requirement?
• A Shipments table has a virtual computed column, TotalWeight, that is calculated from three other columns on every read. Most queries filter on TotalWeight, and plans show full scans that evaluate the expression for every row. Where supported by the DBMS, which change best improves these queries?