CompTIA DataSys+ (DS0-001)Database Management and MaintenanceMedium
A database administrator is conducting a performance tuning exercise. They observe that a critical batch job frequently fails with 'transaction log full' errors, and subsequent database backups take an excessively long time. The database is in FULL recovery model. Which of the following actions should the DBA take to address these issues?
- AIncrease the size of the data files.
- BDisable auto-growth for the transaction log.
- CImplement regular transaction log backups.
- DChange the recovery model to SIMPLE.
Show answer & explanationAnswer & explanation
Correct answer: C. Implement regular transaction log backups.
In the FULL recovery model, the transaction log is not truncated until a log backup occurs. Regular transaction log backups are essential to truncate the log, free up space, prevent it from filling up, and reduce the overall backup time by managing log growth.
Why the other options are wrong
- A. Increasing data file size addresses data storage, not the transaction log's growth or backup issues.
- B. Disabling auto-growth would lead to the log filling up even faster and causing outages if not carefully managed with pre-allocated large files.
- D. Changing to SIMPLE recovery model would truncate the log automatically but sacrifices point-in-time recovery, which is often crucial for critical databases.
Transaction Log Backups (Full Recovery Model)
In a database using the FULL recovery model, transaction log backups are crucial operations that capture all transactions since the last log backup and, upon successful completion, mark inactive portions of the log for truncation, freeing up space.
- Essential for point-in-time recovery.
- Truncates the inactive portion of the transaction log.
- Prevents the transaction log from growing indefinitely.
Memory trick: Log full? Backup the log to clear the path!