ExamTopics Logo
Mail Us[email protected]
Menu
Microsoft Discussions
exam questions

Exam DP-300 All Questions

View all questions & answers for the DP-300 exam
Go to Exam

Exam DP-300 topic 7 question 12 discussion

Actual exam question from Microsoft's DP-300
Question #: 12
Topic #: 7
[All DP-300 Questions]

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

Chosen Answer:
This is a voting comment (?). It is better to Upvote an existing comment if you don't have anything to add.
Switch to a voting comment New
Submit Cancel
2f5c7cd
6 months, 3 weeks ago
I’d go with repair_rebuild and tablock
upvoted 2 times
...
JJJR
11 months, 3 weeks 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
10 months, 1 week 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, 2 months ago
NOINDEX EXTENDED_LOGICAL_CHECKS
upvoted 2 times
...
Community vote distribution
A (35%)
C (25%)
B (20%)
Other
Most Voted
A voting comment increases the vote count for the chosen answer by one.

Upvoting a comment with a selected answer will also increase the vote count towards that answer by one. So if you see a comment that you already agree with, you can upvote it instead of posting a new comment.

SaveCancel
exam
Someone Bought Contributor Access for:
SY0-701
London, 1 minute ago