This set of SQL Server Multiple Choice Questions & Answers (MCQs) focuses on “Working With Transaction Log”.
1. The transaction log supports which of the following operations?
a) Recovery of all incomplete transactions when SQL Server is started
b) Recovery of individual transactions
c) Supporting transactional replication
d) All of the mentioned
View Answer
Explanation: The transaction log also supports high availability and disaster recovery solutions.
2. Point out the correct statement.
a) The Database Engine supports two types of checkpoints
b) Long-running uncommitted transactions decrease recovery time for all types of checkpoints
c) Log truncation deletes inactive virtual log files from the logical transaction log of a SQL Server database
d) All of the mentioned
View Answer
Explanation: Log truncation frees space in the log file for reuse by the transaction log.
3. Which of the log_reuse_wait_desc value has log_reuse_wait value 0?
a) CHECKPOINT
b) ACTIVE_TRANSACTION
c) NOTHING
d) All of the mentioned
View Answer
Explanation: log_reuse_wait value 0 indicates currently there are one or more reusable virtual log files.
4. ACTIVE_TRANSACTION has log_reuse_wait value is _________
a) 1
b) 2
c) 3
d) 4
View Answer
Explanation: A long-running transaction might exist at the start of the log backup. In this case, freeing the space might require another log backup.
5. Point out the wrong statement.
a) For performance reasons, the Database Engine performs modifications to database pages in memory
b) Log truncation does reduce the size of the physical log file
c) Log truncation is essential to keep the log from filling
d) None of the mentioned
View Answer
Explanation: To reduce the physical size of a physical log file, you need to shrink the log file.
6. ________ logging is not supported for memory-optimized tables.
a) Full
b) Minimal
c) Bulk
d) None of the mentioned
View Answer
Explanation: Minimal logging involves logging only the information that is required to recover the transaction without supporting point-in-time recovery.
7. Index page deallocation during a ________ operation is always fully logged.
a) DROP INDEX
b) CREATE INDEX
c) ALTER INDEX
d) None of the mentioned
View Answer
Explanation: The DBCC DBREINDEX statement is deprecated so you should avoid using it in new applications.
8. Which of the following checkpoint has ‘recovery interval’ less than zero?
a) Automatic
b) Direct
c) Indirect
d) All of the mentioned
View Answer
Explanation: Automatic checkpoints whose target recovery interval is specified by the user defined setting of the sp_configure recovery interval option.
9. Internal checkpoint is generated in response to which of the following event?
a) A database snapshot is deleted, whether explicitly or internally for DBCC CHECK
b) Bringing a SQL Server failover cluster instance (FCI) offline
c) Bringing a SQL Server failover cluster instance (FCI) online
d) None of the mentioned
View Answer
Explanation: Internal Checkpoints are generated by various server components to guarantee that disk images match the current state of the log.
10. __________ backups are taken in bulk or full recovery models only.
a) Log
b) Active
c) Passive
d) Replica
View Answer
Explanation: A log backup is required before the transaction log can be truncated. When the next log backup is completed, some log space might become reusable.
Sanfoundry Global Education & Learning Series – SQL Server.
To practice all areas of SQL Server, here is complete set of 1000+ Multiple Choice Questions and Answers.
If you find a mistake in question / option / answer, kindly take a screenshot and email to [email protected]- Apply for Programming Internship
- Check SQL Server Books
- Practice Programming MCQs
- Check Information Technology Books
