Answers to Practice Questions—The Transaction Log

1. Which of the following statements is true for SQL Server 7.0? Select all correct answers?

1. A checkpoint writes all committed transactions to the database unless another process has already written them.
2. A checkpoint writes all changed pages to the database unless another process has already written them.
3. A checkpoint writes all pages that were changed before the preceding checkpoint to the database unless another process has already written them.
4. A checkpoint writes all transactions that were committed before the preceding checkpoint to the database unless another process has already written them.

Correct answers are C and D. For SQL Server 6.5, the correct answers are A and B. But SQL Server 7.0 inserts that extra delay in the write to give other processes a chance to write out the data to the database. C is the best answer because a checkpoint writes out all changes, not just completed transactions. D is only partially correct because it doesn’t explain what happens to pages in currently open transactions.

2. How do you control the frequency of checkpoints in a SQL Server database? Select only one answer?

1. From Enterprise Manager, set the server properties entry for checkpoint interval.
2. From Enterprise Manager, set the server properties entry for recovery interval.
3. From Enterprise Manager, select the database, then set the database properties for checkpoint interval.
4. From Enterprise Manager, select the database, then set the database properties for recovery interval.

The correct answer is B. You don’t set the checkpoint interval directly; instead, you modify it by manipulating the recovery interval. The recovery interval is a server-wide setting, not a database setting.

3. How do you turn off automatic recovery for a database?

1. Set the recovery interval to zero.
2. Run the stored procedure sp_configure to set auto_recovery to off.
3. Start the SQL Server service using net start mssqlserver with no recovery.
4. You can't turn off automatic recovery.

The correct answer is D.

4. Suppose that a transaction starts, then after several checkpoints, the system fails. Which one of the following statements correctly describes what happens in the recovery process as the server restarts?

1. The recovery process doesn’t have to do anything. Because the transaction wasn't committed yet, it wasn't written out at the checkpoints.
2. In SQL Server 7.0, the recovery process must undo everything written to the database for this transaction from the checkpoint before the last one.ss must undo all changes written to the database for this transaction at each previous checkpoint.
3. The recovery process must undo all changes written to the database for this transaction at each previous checkpoint.
4. The recovery process must undo all changes written to the database for this transaction by any process, including checkpoints.

The correct answer is D. Other processes, including the lazywriter, may have written to the database, and you must remove any changes.