Term
| What two methods of PAGE_VERIFY exist |
|
Definition
CHECKSUM
TORN_PAGE_DETECTION |
|
|
Term
| What is the default option for PAGE_VERIFY? |
|
Definition
CHECKSUM
The others options are TORN_PAGE_DETECTION and NONE |
|
|
Term
| Will SQL Server verify a checksum or torn page during a read if teh value is present on the page header when PAGE_VERIFY is set to NONE? |
|
Definition
| No. Database pages writes will not generate a CHECKSUM or TORN_PAGE_DETECTION value and will not verify the value if it exists on the page header |
|
|
Term
| When SQL Server encounters a corrupt page what events happen? |
|
Definition
1. Error is thrown
2. Command attempting to access the page is aborted
3. Entry is written into the suspect_pages table in msdb |
|
|
Term
| When is the CHECKSUM written to the page header? |
|
Definition
| When the page is written to disk |
|
|
Term
| What commands does DBCC CHECKDB execute? |
|
Definition
| DBCC CHECKDB performs the same checking as if both a DBCC CHECKALLOC statement and a DBCC CHECKTABLE statement were executed for each table in the database.** |
|
|
Term
| What does DBCC CHECKDB check for each table? |
|
Definition
Index and data pages are correctly linked
Indexes are in their proper sort order
Pointers are consistent
The data on each page is reasonable
Pages offsets are reasonable** |
|
|
Term
| What are the 3 repair options that can be specified for DBCC CHECKDB |
|
Definition
REPAIR_ALLOW_DATA_LOSS
REPAIR_FAST
REPAIR_REBUILD |
|
|
Term
| Who has permission to execute DBCC CHECKDB? |
|
Definition
| members of the sysadmin server role or the db_owner database role** |
|
|
Term
| What are the benefits to specify TABLOCK during DBCC CHECKDB? |
|
Definition
| It allows DBCC CHECKDB to acquire shared table locks. This allows more detailed error messages for some classes of errors and minimizes the amount of tempdb space required by avoiding the use of transaction log data. The TABLOCK option will not block the truncation of the log and will allow the command to run faster.** |
|
|
Term
| If NOINDEX is specified does DBCC CHECKDB always checks all system table indexes? |
|
Definition
| Yes. NOINDEX specifies that nonclustered indexes for nonsystem tables should not be checked. NOINDEX decreases the overall execution time because it does not check nonclustered indexes for user-defined tables. NOINDEX has no effect on system tables, they will always be checked.** |
|
|
