DP-300 · Question #204
Hotspot Question You have a 50-TB Microsoft SQL Server database named DB1. You need to reduce the time it takes to perform database consistency checks of DB1. Which Transact-SQL command should you…
The correct answer is DBCC CHECKDB ([DB1],: NOINDEX; with: PHYSICAL_ONLY. For a 50-TB SQL Server database, running a full DBCC CHECKDB is extremely time-consuming. The PHYSICAL_ONLY option significantly reduces check duration by limiting scope to physical structure integrity only.
Question
Exhibit
Answer Area
- DBCC CHECKDB ([DB1],NOINDEXNOINDEXREPAIR_FASTREPAIR_REBUILD
- withPHYSICAL_ONLYALL_ERRORMSGSNO_INFOMSGSPHYSICAL_ONLY
Explanation
For a 50-TB SQL Server database, running a full DBCC CHECKDB is extremely time-consuming. The PHYSICAL_ONLY option significantly reduces check duration by limiting scope to physical structure integrity only.
Approach. The correct command is DBCC CHECKDB ('DB1') WITH PHYSICAL_ONLY. The PHYSICAL_ONLY option skips all logical consistency checks (allocation structures, cross-object consistency, table data) and only validates the physical structure of each page and record headers. For very large databases (10+ TB), this is Microsoft's recommended approach to reduce maintenance window impact while still detecting the most common hardware-related corruptions such as torn pages, checksum failures, and disk I/O errors. A full DBCC CHECKDB on 50 TB can run for days, while PHYSICAL_ONLY can complete in a fraction of that time. Microsoft recommends running PHYSICAL_ONLY frequently and a full DBCC CHECKDB periodically for large databases.
Concept tested. DBCC CHECKDB options for large SQL Server databases - specifically using PHYSICAL_ONLY to reduce consistency check duration while maintaining hardware-level integrity validation
Reference. https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkdb-transact-sql
Topics
Community Discussion
No community discussion yet for this question.