Microsoft 70-432 Online Practice Questions and Exam Preparation

Microsoft 70-432 Online Questions & Answers

• Question 181:

  You administer a Microsoft SQL Server 2008 R2 database that contains an OrderItems table. The table has the following definition:

  

  Data is grouped into quarterly partitions.

  You need to configure the groupings into 12 monthly partitions.

  What should you do?

  A. Remove the clustered index from the table.
  B. Use the ALTER PARTITION FUNCTION ... SPLIT RANGE statement.
  C. Use the ALTER TABLEstatement to remove the COLLATEoption.
  D. Execute the DBCC CLEANTABLEcommand on the OrderItems table.
  E. Create a new filegroup. Create a new database file. Use the ALTER PARTITION SCHEME statement along with the NEXT USED clause. Use ALTER INDEX REORGANIZE statement.
  F. Create a new Filegroup. Create a new database File. Use the ALTER PARTITION SCHEME statement along with the NEXT USED clause. Use the ALTER PARTITION FUNCTION statement along with the SPLIT RANGE clause.
  G. Create a new table. Use the ALTER TABLE statement along with the SWITCH PARTITION clause. Use the ALTER PARTITION FUNCTION statement along with the MERGE RANGE clause.
  H. Create a new partition function. Create a new partition scheme. Add a clustered index to place the data onto the partition scheme.
  I. Run the following statement: CREATE PARTITION SCHEME SEC_FG AS PARTITION FUNC_FG ALL TO (SECONDARY);
  J. Run the following statement: EXECUTE sp_tableoption @TableNamePattern ='OrderItem3', @OptionName= `PartltionByYear'; @OptionValue= 'true';
  B. Use the ALTER PARTITION FUNCTION ... SPLIT RANGE statement.

• Question 182:

  You administer a Microsoft SQL Server database that hosts an order-processing application.

  Employees in the order entry group perform real-time data entry for customers. Employees in the reporting group run historical reports.

  Employees in the order entry group report of poor performance when the reporting group runs reports.

  You need to limit the amount of CPU and RAM used by the reporting group.

  What should you do?

  A. Implement Resource Governor by using a Resource Pool assigned to the users of the reporting group to limit the amount of CPU/RAM they can use.
  B. Use sp_dbmmonitoraddmonitoring to set up a monitoring process to kill the reporting group's queries that pass a given threshold.
  C. Create a set of views in a new schema, and then alter the queries of the reporting group to use these views.
  D. Implement Policy Based Management by using the Server Performance facet.
  C. Create a set of views in a new schema, and then alter the queries of the reporting group to use these views.

• Question 183:

  You administer a SQL Server 2008 instance. You have a stored procedure that implements a database maintenance process.

  You need to create a SQL Server Agent job that runs the stored procedure. You also need to ensure that the job is removed after successful completion.

  What should you do?

  A. Create a job that is scheduled to run once.
  B. Create a job that uses the automatically delete job option.
  C. Create a job that is assigned to the Database Maintenance category.
  D. Create an Alert that will be raised when the job completes. This Alert will run another job to delete the maintenance job.
  B. Create a job that uses the automatically delete job option.

• Question 184:

  You administer several SQL Server 2008 instances.

  You need to ensure that all SQL Server instances are consistently configured for the following aspects:

  What should you do?

  A. Use the Database Engine Tuning Advisor.
  B. Use the SQL Server Configuration Manager.
  C. Create a policy in Microsoft SQL Server Management Studio.
  D. Create a maintenance plan in Microsoft SQL Server Management Studio.
  C. Create a policy in Microsoft SQL Server Management Studio.

• Question 185:

  You upgrade an instance of Microsoft SQL Server 2000 to SQL Server 2008.

  The SQL Server Agent service is configured to use the LocalSystem account. A job uses a CMDExec step to access a file located on a remote network share.

  After the upgrade, the job step fails to complete execution.

  You need to configure the job step so that it can successfully complete execution.

  What should you do?

  A. Configure a certificate.
  B. Configure the job step to use a proxy account.
  C. Configure the SQL Server Agent service to use a local Windows account.
  D. Configure the SQL Server Agent service to use the NetworkService account.
  B. Configure the job step to use a proxy account.

• Question 186:

  You administer a SQL Server 2008 instance that contains a database named AdventureWorks. The AdventureWorks database contains a table named Orders. The Orders table has several indexes and a very large number of rows. The database supports an enterprise Web environment that is constantly used.

  Thirty percent of the indexes on the Orders table is fragmented.

  You need to defragment the indexes by ensuring that the effect on database availability is minimized. Which option should you include in the ALTER INDEX statement?

  A. Online
  B. Fill factor
  C. Pad index
  D. Sort in tempdb
  A. Online

• Question 187:

  You administer a SQL Server 2008 instance that contains a database named DB1.

  The DB1 database contains the following stored procedure. (Line numbers are included for reference only.)

  When a user named User1 attempts to invoke Procedure1, the following exception is raised:

  "Msg 262, Level 14, State 1, Procedure Procedure1, Line 5 CREATE TABLE permission denied in database 'DB1'."

  You need to provide User1 access to execute Procedure1 by allocating only the required permissions.

  What should you do?

  A. Grant the ALTER permission on the Sales schema to User1.
  B. Grant the CREATE TABLE permission and allow User1 to drop the Sales.Table1 table.
  C. Insert the WITH EXECUTE AS 'dbo' clause between lines 01 and 02.
  D. Insert the EXECUTE AS USER = 'dbo' statement between lines 02 and 03.
  C. Insert the WITH EXECUTE AS 'dbo' clause between lines 01 and 02.

• Question 188:

  You configure a SQL Server 2008 instance that contains a database named Customers. The Customers database experiences deadlock problems.

  You need to capture the deadlock information to the SQL Server error log.

  What should you do?

  A. Enable file system auditing.
  B. Enable the Windows Event Collector service.
  C. Configure trace flags and then restart the SQL Server instance.
  D. Configure a Data Collector Set and then restart the SQL Server instance.
  C. Configure trace flags and then restart the SQL Server instance.

• Question 189:

  You administer a Microsoft SQL Server 2008 R2 instance. You need to check the physical consistency of the database.

  What should you do?

  A. Execute sp_helpfile.
  B. Execute DBCC CHECKDB.
  C. Examine the msdb..suspect_pagestable.
  D. Execute DBCC CHECKDBalong with the REPAIR_FASTclause.
  E. Execute DBCC CHECKDB along with the REPAIR_REBUILDcla.se
  F. Restore the database from the most recent full backup. Apply any differential and log backups.
  G. Use the ALTER DATABASEstatement along with the SET EMERGENCYclause.
  H. Use the RESTORE DATABASEstatement along with the PAGESclause. Create a new log backup. Apply all differential and log backups, including the most recent backup.
  I. Use the RESTORE DATABASEstatement along with the PAGESclause. Apply any differential and log backups. Create a new log backup and then restore the new log backup.
  B. Execute DBCC CHECKDB.

• Question 190:

  You administer a SQL Server 2008 instance that contains the AdventureWorks database. The Adventure Works database uses the bulk-logged recovery mode.

  You log ship the AdventureWorks database across a WAN link.

  There is a scheduled job that rebuilds the indexes of the AdventureWorks database.

  You discover that the job increases the size of the log backups for log shipping.

  You need to minimize the size of the log backups used by log shipping.

  What should you do?

  A. Compress the log file backups.
  B. Change the job to drop the indexes, and then recreate the indexes.
  C. Alter the AdventureWorks database to use the Full recovery mode.
  D. Alter the AdventureWorks database to use the Simple recovery mode.
  A. Compress the log file backups.