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.

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.

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 off.
3. Start the SQL Server service using net start mssqlserver with no recovery.
4. You can't turn off automatic recovery.

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.
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.