The Real MTCS SQL Server 2008 Exam 70/432 Prep Kit- P150: Congratulations on your journey to become certified in SQL Server 2008. This book will help prepare you for your exam and give you a practical view of working with SQL Server 2008. | 50 Appendix Self Test Appendix operations without losing progress in case the maintenance operation affects performance severely while it is running. What is the best way to resolve the problem while meeting your objectives A. Run ALTER INDEX _Orders_CustomerID ON REBUILD B. Run ALTER INDEX IX_Orders_CustomerID ON REORGANIZE C. Run CREATE INDEX IX_Orders_CustomerID ON Orders. CustomerID WITH DROP EXISTING D. Run DBCC INDEXDEFRAG LOBDatabase Orders IX_Orders_ CustomerID Correct Answer Explanation B. Specifying the REORGANIZE option with the ALTER INDEX statement slowly rearranges the index in the background without taking it offline. It also allows you to cancel the operation without losing the index and without losing any of the defragmentation that has already taken place. Incorrect Answers Explanations A C and D. Answer A is incorrect because specifying the REBUILD option with the ALTER INDEX statement will force the index to be taken offline. Answer C is incorrect because dropping and re-creating the index will make the index unavailable to users until the CREATE statement has completed which may take a long time. Answer D is incorrect because DBCC INDEXDEFRAG is for backwards compatibility only and should not be used in SQL Server 2008. 2. You have recently removed a lot of historical data from your SQL Server 2008 database named HR by archiving it to a different server. However you notice that the disk space consumed by the database file remains unchanged and the . MDF file is taking up the majority of space on the drive. How can you decrease the size of the database file A. Run DBCC CHECKDB HR REPAIR_ALLOW_DATA_LOSS B. Run DBCC SHRINKFILE HR_Data TRUNCATEONLY C. Run DBCC SHRINKFILE HR_Data NOTRUNCATE D. Use the Copy Database Wizard to copy the database then delete the original database. Finally rename the new database you have just copied to the same name as the original database. Self Test Appendix Appendix 51 Correct .
