nerdexam
Microsoft

70-450 · Question #132

You are a professional level SQL Sever 2008 Database Administrator. A large database is hosted by the instance. The company utilizes the following backup strategy for the database. First, the…

The correct answer is D. To finish the task, the NCIX index should be dropped and recreated. As-you can see in the DBCC CHEYZKDB, the failed object is the index with the ID=2. Or, the index with the |D=2 is the index NCIX, one nonclustered index. So, this index is build upon the Cluster index and all the leaf pages contains the leaf page address of the clustered index…

Design an optimized high availability and disaster recovery solution

Question

You are a professional level SQL Sever 2008 Database Administrator. A large database is hosted by the instance. The company utilizes the following backup strategy for the database. First, the performance of a full database backup is implemented at 02:00 hr every Monday. Secondly, the performance of a differential backup is implemented at 23:00 hr every day. Thirdly, the performance of a transaction log backup is implemented on an hourly basis. A reboot is caused on the SQL Server 2008 server by a power failure at 09:15 hr, Thursday. Fifteen minutes after the reboot of the server, it is reported that certain queries that access customer data cannot be executed. You find that the customer data remains unvaried after the power failure. When the DBCC CHECKDB command is executed on the database, the following error message is received. Object ID 2814307832, index ID 2, partition ID 83127819437122157, alloc unit ID 82134587923221126 (type In-row data): Page (3421:169) could not be processed. See other errors for details. Table error:

Object ID 2814307832, index ID 2, partition ID 83127819437122157, alloc unit ID 82134587923221126 (type In-row data), page (3421:169). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 16928422 and -8.CHECKDB found 0 allocation errors and 2 consistency errors in table 'tbl_Customer' (object ID 2814307832). When the sp_help 'tbl_customer' stored procedure is executed, the following result set is received:

index_name index_description PK clustered located on PRIMARY NCIX nonclustered located on PRIMARY Since you are the technical support, you are required to make sure that the data should be accessible in the shortest time. And the impact on users should be cut to the least. Which action will you perform to finish the task?

Options

  • ATo finish the task, the PK index should be dropped and recreated.
  • BTo finish the task, the latest full database backup should be recovered.
  • CTo finish the task, the latest full database backup should be recovered.
  • DTo finish the task, the NCIX index should be dropped and recreated.

How the community answered

(28 responses)
  • A
    14% (4)
  • B
    11% (3)
  • C
    4% (1)
  • D
    71% (20)

Explanation

As-you can see in the DBCC CHEYZKDB, the failed object is the index with the ID=2. Or, the index with the |D=2 is the index NCIX, one nonclustered index. So, this index is build upon the Cluster index and all the leaf pages contains the leaf page address of the clustered index. To rebuild this index in the fatsest way, the solution is to drop it and to re-create it after.

Topics

#disaster recovery#index corruption#backup strategy#NCIX index

Community Discussion

No community discussion yet for this question.

Full 70-450 Practice