HOTSPOT -
You have a SQL Server on Azure Virtual Machines instance that hosts a 10-TB SQL database named DB1.
You need to identify and repair any physical or logical corruption in DB1. The solution must meet the following requirements:
✑ Minimize how long it takes to complete the procedure.
✑ Minimize data loss.
How should you complete the command? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:
Box 1: REPAIR_REBUILD -
REPAIR_REBUILD -
Performs repairs that have no possibility of data loss. This option may include quick repairs, such as repairing missing rows in nonclustered indexes, and more time-consuming repairs, such as rebuilding an index.
Box 2: PHYSICAL_ONLY -
PHYSICAL_ONLY -
Limits the checking to the integrity of the physical structure of the page and record headers and the allocation consistency of the database. This check is designed to provide a small overhead check of the physical consistency of the database, but it can also detect torn pages, checksum failures, and common hardware failures that can compromise a user's data.
Incorrect:
TABLOCK -
Causes DBCC CHECKDB to obtain locks instead of using an internal database snapshot. This includes a short-term exclusive (X) lock on the database.
TABLOCK will cause DBCC CHECKDB to run faster on a database under heavy load, but will decrease the concurrency available on the database while DBCC
CHECKDB is running.
EXTENDED_LOGICAL_CHECKS -
If the compatibility level is 100 ( SQL Server 2008) or higher, performs logical consistency checks on an indexed view, XML indexes, and spatial indexes, where present.
Reference:
https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkdb-transact-sql
The answer is correct.
NOINDEX - only check, no repair EXTENDED_LOGICAL_CHECKS - time-consuming TABLOCK - lock the whole table, time-consuming
but PHYSICAL_ONLY will skip logical check right? the question state that it need both physical and logical repair. and how tablock time consuming? it avoid other load using the table while checking. isnt that supposed to make it faster?
Perform logical consistency checks on indexes Logical consistency checking on indexes varies according to the compatibility level of the database, as follows: If the compatibility level is at least 100 (introduced in SQL Server 2008 (10.0.x)): Unless NOINDEX is specified, DBCC CHECKDB performs both physical and logical consistency checks on a single table and on all its nonclustered indexes. However, on XML indexes, spatial indexes, and indexed views only physical consistency checks are performed by default. If WITH EXTENDED_LOGICAL_CHECKS is specified, logical checks are performed on an indexed view, XML indexes, and spatial indexes, where present. By default, physical consistency checks are performed before the logical consistency checks. If NOINDEX is also specified, only the logical checks are performed. answer is correct
It cant be NOINDEX or REPAIR ALLOW DATA LOSS. REPAIR FAST- Backward compatible, no actual Repair SO only Correct Option : Repair_Rebuild 2nd Box, I will go with Physical_only. EXTENDED logical will increase the time too much, tablock doesnt make sense here
NOINDEX EXTENDED_LOGICAL_CHECKS
Duplicate of https://www.examtopics.com/discussions/microsoft/view/75039-exam-dp-300-topic-7-question-10-discussion/ there the answer is Box 1: NOINDEX - NOINDEX specifies that intensive checks of nonclustered indexes for user tables will not be performed. This choice decreases the overall execution time. NOINDEX doesn't affect system tables because integrity checks are always performed on system table indexes. Box 2: PHYSICAL_ONLY -
they are different, the key in this question: ✑ Minimize how long it takes to complete the procedure. (physical only) ✑ Minimize data loss (Repair_Rebuild) So I think the answer is correct with those 2 requirements
The question says "You need to identify and repair any physical or logical corruption in DB1", so if we select the physical option, will it also fix the logical corruption?
I think Box 2 should be 'extended_logical_checks' "If SQL Server messes up something like an index rebuild or an indexed view, that logical corruption will absolutely flow through to the secondary copies. You have to run DBCC CHECKDB everywhere!" https://www.sqlskills.com/blogs/paul/the-curious-case-of-whether-corruption-can-propagate-to-secondary-databases/
Adding this documentation for why it's NOT REPAIR_FAST: REPAIR_FAST Maintains syntax for backward compatibility only. No repair actions are performed. https://learn.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkdb-transact-sql?view=sql-server-ver16#repair_allow_data_loss--repair_fast--repair_rebuild