Exam DP-300 All Questions

View all questions & answers for the DP-300 exam

Exam DP-300 topic 7 question 12 discussion

Actual exam question from Microsoft's DP-300

Question #: 12
Topic #: 7

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:

Show Suggested Answer

Suggested Answer:

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

by Imran36 at Oct. 25, 2022, 10:01 a.m.

Comments

Submit Cancel

2f5c7cd

6 months ago

I’d go with repair_rebuild and tablock

upvoted 2 times

...

JJJR

11 months ago

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

upvoted 1 times

...

drgr123

1 year, 1 month ago

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

upvoted 2 times

Sr18

9 months, 2 weeks ago

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

upvoted 1 times

...

U_C

2 years ago

The answer is correct.

upvoted 3 times

U_C

2 years ago

NOINDEX - only check, no repair EXTENDED_LOGICAL_CHECKS - time-consuming TABLOCK - lock the whole table, time-consuming

upvoted 2 times

asduta

1 year, 10 months ago

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?

upvoted 1 times

...

KIET2131

2 years, 1 month ago

NOINDEX EXTENDED_LOGICAL_CHECKS

upvoted 2 times

...

Imran36

2 years, 5 months ago

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 -

upvoted 2 times

Saffar

2 years, 5 months ago

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

upvoted 8 times

Fer079

2 years, 3 months ago

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?

upvoted 2 times

OneplusOne

2 years, 3 months ago

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/

upvoted 2 times

...

Exam DP-300 All Questions

View all questions & answers for the DP-300 exam

Exam DP-300 topic 7 question 12 discussion

Comments

2f5c7cd

JJJR

drgr123

Sr18

U_C

U_C

asduta

KIET2131

Imran36

Saffar

Fer079

OneplusOne

SY0-701