Learn with Microsoft : 70-432 training material for 100% pass

Microsoft 70-432 Exam Overview:

Microsoft 70-432 Exam Syllabus Topics:

Microsoft TS:MS SQL Server 2008,Implementation and Maintenance Sample Questions:

1. You administer a Microsoft SQL Server 2008 R2 database that contains an OrderItems table.
The table has the following definition: Currently, the table is not partitioned and contains no indexes.

You need to partition the table by year.
What should you do?

A) Use the ALTER PARTITION FUNCTION ... SPLIT RANGE statement.
B) 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.
C) 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.
D) Run the following statement: EXECUTE sp_tableoption @TableNamePattern ='OrderItem3', @OptionName= 'PartltionByYear'; @OptionValue= 'true';
E) Remove the clustered index from the table.
F) Use the ALTER TABLEstatement to remove the COLLATEoption.
G) Run the following statement: CREATE PARTITION SCHEME SEC_FG AS PARTITION FUNC_FG
ALL TO (SECONDARY);
H) Create a new partition function.
Create a new partition scheme.
Add a clustered index to place the data onto the partition scheme.
I) Execute the DBCC CLEANTABLEcommand on the OrderItems table.
J) 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.

2. You configure a SQL Server 2008 instance that contains a database named AdventureWorks.
The AdventureWorks database experiences deadlock problems.
You need to capture the deadlock information to the SQL Server error log.
What should you do?

A) Configure the data collector to capture the deadlock graphs.
B) Set the appropriate trace flags as a startup parameter and restart the SQL Server instance.
C) Enable Server Auditing for the AdventureWorks database.
D) Configure a SQL Profiler trace to capture the deadlock graphs.

3. View Exhibit

A) Use the Activity Monitor in Microsoft SQL Management Studio and locate process 55.
B) Execute sp_who 55.
C) Execute sp_who 56.
D) Select from the sys.syslockinfo compatibility view and locate spid 55.

4. You administer a SQL Server 2008 instance.
A stored procedure is used as the data source for a report that is frequently executed during business hours. Users report that the data returned by the report is inconsistent. You discover that the
inconsistencies are caused by phantom reads.
You need to ensure that the report returns consistent results without affecting other users.
What should you do?

A) Modify the stored procedure to use the Repeatable Read isolation level.
B) Modify the stored procedure to use the Snapshot isolation level.
C) Configure the database for Auto Update Statistics asynchronously.
D) Configure the database for Read Committed Snapshot isolation.

5. DRAG DROP
You administer a Microsoft SQL Server 2008 R2 database instance. The service account used by SQL Server services must not have administrative permissions.
You configure a new SQL Server Agent job to run every night. One of the steps in the job runs a PowerShell step. The job continuously fails on this step and throws the following error message:
"The process could not be created for step 1 of job (reason: A required privilege is not held by the client). The step failed."
You need to ensure that the SQL Server Agent Job executes successfully.
Which four actions should you perform in sequence? (To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.)

Solutions: