CompTIA DataSys+ (DS0-001)Database Management and MaintenanceHard
A database administrator is tasked with optimizing a critical batch process that involves inserting millions of rows into a large table daily. The process is currently slow due to excessive logging and I/O. The database system allows for different recovery models or logging modes. Which recovery model or logging mode should the administrator consider to minimize logging overhead during this specific bulk insert operation, while still allowing for full point-in-time recovery for the rest of the database?
- ASimple Recovery Model
- BFull Recovery Model
- CEmergency Recovery Model
- DBulk-Logged Recovery Model
Show answer & explanationAnswer & explanation
Correct answer: D. Bulk-Logged Recovery Model
The Bulk-Logged recovery model (in SQL Server, or similar concepts in other databases) allows for minimal logging for bulk operations like large inserts, reducing transaction log overhead and improving performance, while still supporting point-in-time recovery for transactions not minimally logged. This is a compromise between full logging and no logging.
Why the other options are wrong
- A. Simple Recovery Model does not allow point-in-time recovery for any transaction, as the log is truncated automatically, making it unsuitable for the 'rest of the database' requirement.
- B. Full Recovery Model logs all transactions fully, which is good for point-in-time recovery but causes high overhead for bulk inserts.
- C. Emergency Recovery Model is a specific state for database repair, not a general recovery model for performance optimization.
Bulk-Logged Recovery Model
A database recovery model (specific to SQL Server, but conceptually similar in others) that provides a balance between full logging and minimal logging, allowing certain bulk operations to be minimally logged for performance.
- Minimizes transaction log space for bulk operations.
- Still supports point-in-time recovery for non-bulk operations.
- Requires log backups to truncate the log.
Memory trick: Bulk-Logged: A 'bulk' deal for speed, but logs enough for recovery.