Latest MCTS 70-432 Dumps Real Exam Download 1-10

Ensurepass

QUESTION 1

You maintain a SQL Server instance.

You use the Resource Governor to manage processor resources on the server for a newly deployed application named App1. App1 is assigned to a custom workload group named MyApp.

You need to monitor processor resources.

What should you do?

 

A. Use the sys.dm_os_performance_counters to monitor the CPU usage % counter of the SQLServer: Workload Group Stats performance object for the MyApp workload group.

B. Use the request_max_cpu_time_sec column of the sys.resource_governor_workload_groups catalog view

C. Use the sys.dm_resource_governor_workoad_groups dynamic management view to monitor the CPU usage for the default workload group

D. Use the sys_dm_os_performance_counters to monitor the CPU usage % counter of the SQLServer: Workload Group Stats performance object for the default workload group

 

Correct Answer: A

Explanation/Reference:

 

 

QUESTION 2

You administer a Microsoft SQL Server 2008 database for an order-processing application. Users report intermittent poor performance. You identify the cause as large reports being run by members of the Reporting group. The following Transact-SQL statements have been run against the database: CREATE TABLE UserClassification (GroupName SYSNAME UserName SYSNAME ); INSERT into UserClassification VALUES (’ProductionGroup’, ‘User1’), (‘ProductionGroup’, ‘User2’), (‘Reporting Group’, ‘User3’), (‘Reporting Group’, ‘User4’); CREATE FUNCTION UserClassifier () RETURNS SYSNAME WITH SCHEMABINDING AS BEGIN DECLARE @group SYSNAME SELECT TOP 1 @group = uc.GroupName FROM dbo.UserClassification uc WHERE uc.UserName = SUSER_SNAME () If (@Group IS NOT NULL) BEGIN RETURN @Group END RETURN N’default’ END; You need to ensure that members of the reporting group can use only up to 35 percent of the CPU and up to 45 percent of the available RAM.

Select and Place: 

clip_image002

Correct Answer:

clip_image004

Explanation/Reference:

Feedback:

…Because it is the REPORTING group that generates the large reports that can now use only up to 35%

CPU and 45% RAM.

https://www.google.es/url?sa=t&rct=j&q=&esrc=s&source=web&cd=4&cad=rja&ved=0CFkQFjAD&url=http %3A%2F%2Fdownload.microsoft.com%2Fdownload%2FD%2FB%2FD%2FDBDE7972-1EB9-470A-BA18-58849DB3EB3B%2FResourceGov. docx&ei=1pjcULqtD47BswbE9YCoAQ&usg=AFQjCNEESyvR6ppfxi02wgN9EWGKnBbVcA&bvm=bv.1355 534169,d.Yms http://msdn.microsoft.com/en-us/library/bb933866(v=sql.105).aspx http://msdn.microsoft.com/en-us/library/cc645892(v=sql.105).aspx

 

 

QUESTION 3

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 (Operating System – CmdExec) 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.)

Select and Place: 

clip_image006

Correct Answer:

clip_image008Explanation/Reference:

 

 

QUESTION 4

Note: This question is part of a series of questions that use the same or similar answer choices. Ananswer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.

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_pages table.

D.            Execute DBCC CHECKDB along with the REPAIR_FAST clause.

E.            Execute DBCC CHECKDB along with the REPAIR_REBUILD clause.

F.             Restore the database from the most recent full backup. Apply any differential and log backups.

G.            Use the ALTER DATABASE statement along with the SET EMERGENCY clause.

H.            Use the RESTORE DATABASE statement along with the PAGE clause. Create a new log backup. Apply all differential and log backups, including the most recent backup.

I.             Use the RESTORE DATABASE statement along with the PAGE clause. Apply any differential and log backups. Create a new log backup and then restore the new log backup.

 

Correct Answer: B

Explanation/Reference:

 

 

QUESTION 5

Note: This question is part of a series of questions that use the same or similar answer choices. Ananswer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.

You administer a Microsoft SQL Server 2008 R2 instance. You need to rectify a damaged index without losing any data. What should you do?

 

A.            Execute sp_helpfile.

B.            Execute DBCC CHECKDB.

C.            Examine the msdb…suspect_pages table.

D.            Execute DBCC CHECKDB along with the REPAIR_FAST clause.

E.            Execute DBCC CHECKDB along with the REPAIR_REBUILD clause.

F.             Restore the database from the most recent full backup. Apply any differential and log backups.

G.            Use the ALTER DATABASE statement along with the SET EMERGENCY clause.

H.            Use the RESTORE DATABASE statement along with the PAGE clause. Create a new log backup. Apply all differential and log backups, including the most recent backup.

I.             Use the RESTORE DATABASE statement along with the PAGE clause. Apply any differential and log backups. Create a new log backup and then restore the new log backup.

 

Correct Answer: E

Explanation/Reference:

 

 

QUESTION 6

Note: This question is part of a series of questions that use the same or similar answer choices. Ananswer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.

You administer a Microsoft SQL Server 2008 R2 instance.

A disk drive that contains the files for the database is lost. The drive has been replaced, but the data files have been irrevocably lost.

You need to restore the database.

What should you do?

 

A.            Execute sp_helpfile.

B.            Execute DBCC CHECKDB.

C.            Examine the msdb…suspect_pages table.

D.            Execute DBCC CHECKDB along with the REPAIR_FAST clause.

E.            Execute DBCC CHECKDB along with the REPAIR_REBUILD clause.

F.             Restore the database from the most recent full backup. Apply any differential and log backups.

G.            Use the ALTER DATABASE statement along with the SET EMERGENCY clause.

H.            Use the RESTORE DATABASE statement along with the PAGE clause. Create a new log backup. Apply all differential and log backups, including the most recent backup.

I.             Use the RESTORE DATABASE statement along with the PAGE clause. Apply any differential and log backups. Create a new log backup and then restore the new log backup.

 

Correct Answer: F

Explanation/Reference:

 

 

QUESTION 7

You administer a Microsoft SQL Server 2008 R2 database instance.

Occasionally, the following problems occur:

The SQL Server service fails to start.

A query from the application runs slower than expected.

You need to be able to diagnose each of these problems.

Which tool or tools should you use? (To answer, drag the appropriate problem to the correct tool in the answer area. Each problem may be used once, more than once, or not at all. Additionally, you may need to drag the split bar between panes or scroll to view content.)

[Click “Select and Place”; Click ‘RESET’ to correct your answer choices.]

Select and Place: clip_image010

Correct Answer: clip_image012

Explanation/Reference:

http://blogs.msdn.com/b/sqlserverfaq/archive/2011/05/11/inf-hey-my-sql-server-serviceis-not-starting-what-do-i-do.

Feedback and dispute:

If the SQL Server service fails to start, i think the correct tool is the Windows Application Log and no more.

Dynamic Management Views, SQL Server Profiler, Activity Monitor and SQL Server ErrorLOG depends on SQL Server service, so, I think the correct tool to find information is the Windows Application Log.

I think the corrects tools for the other question are: SQL Server Profiler, Activity monitor and Dynamic Management Views.

 

 

QUESTION 8

You administer three SQL Server 2008 instances on separate servers named Server1, Server2 and

Server3.

The AdventureWorks database is configured for mirroring between the instances. In the mirroring session, Sever1 and Server2 act as partners and Server3 as the witness.

You have the following requirements:

All three servers run a maintenance process that requires occasional restart of the computer.

The SQL Server service on each server is shut down only during the actual restart of the computer.

After completion of the maintenance process, re-enable automatic failover and synchronize the mirroring session in minimum possible time.

You need to ensure that automatic failover is disabled during the maintenance process.

What should you do first?

 

A. Suspend the mirroring session.

B. Configure the mirroring session with the SAFETY option set to ON.

C. Remove the witness server from the mirroring session.

D. Remove mirroring from the database.

 

Correct Answer: C

Explanation/Reference:

Feedback:

Because the need of ensure that automatic failover is disabled.

http://msdn.microsoft.com/en-us/library/ms189590(v=sql.105).aspx

At any time during a database mirroring session, the database owner can turn off (remove) the witness for a database mirroring session.

Turning off the witness changes the operating mode in accordance with the transaction-safety setting:

If transaction safety is set to FULL (the default), the session uses high-safety, synchronous mode without automatic failover. For more information, see Synchronous Database Mirroring (High-Safety Mode).

If transaction safety is set to OFF, the session operates asynchronously (in high-performance mode) without requiring quorum. Whenever transaction safety is turned off, we strongly recommend also turning the witness off. For more information, see Asynchronous Database Mirroring (High-Performance Mode). The transaction safety setting of the database is recorded on each partner in the sys.database_mirroring catalog view in the mirroring_safety_level and mirroring_safety_level_desc columns. For more information, see sys.database_mirroring (Transact-SQL).

To turn off (remove) the witness

Connect to either partner.

Issue the following statement:

ALTER DATABASE <database_name> SET WITNESS OFF where <database_name> is the name of the mirrored database.

 

 

QUESTION 9

You administer two SQL Server 2008 instances on separate servers named Server1 and Server2.

The AdventureWorks database is set up for synchronous mirroring between the two instances.

You need to configure the database to support automatic failover.

What should you do?

 

A. Configure the mirroring session by using the FAILOVER option.

B. Configure the mirroring session by adding a witness server to it.

C. Configure the mirroring session with the SAFETY option set to OFF.

D. Configure the mirroring session with the SAFETY option set to FULL.

 

Correct Answer: B

Explanation/Reference:

Correct answer is: Configure the mirroring session by adding a witness server to it.

—From Rob:

By default a session is set to full transaction safety (SAFETY is set to FULL), which starts the session in synchronous, high-safety mode without automatic failover. You can reconfigure the session to run in highsafety mode with automatic failover or in asynchronous, high-performance mode, as follows:

.High-safety mode with automatic failover If you want a high-safety mode session to support automatic failover, add a witness server instance. Source:http://msdn.microsoft.com/en-us/library/ms190941.aspx

 

 

QUESTION 10

You administer a Microsoft SQL Server 2008 R2 instance that contains two databases named

Northwind2008R2 and AdventureWorks.

The Northwind2008R2 database uses the SQL_Latin1_General_CP1_CI_AS collation and the

AdventureWorks database uses the SQL_Latin1_General_CP1_CS_AS collation.

You need to write a query that returns all customers having the same name in each database. You also need to ensure that the query is not case-sensitive.

Which Transact-SQL query should you use?

 

A. SELECT * FROM AdventureWorks.dbo.customer adv INNER JOIN Northwind2008R2.dbo.customer nrt ON adv.CustomerName = nrt.CustomerName COLLATE SQL_Latin1_General_CP1_CI_AS;

B. SELECT * FROM AdventureWorks.dbo.customer adv INNER JOIN Northwind2008R2.dbo.customer nrt ON adv.CustomerName = nrt.CustomerName USING COLLATION SQL_Latin1_General_CP1_CI_AS;

C. SET COLLATE SQL_Latin1_General_CP1_CI_AS; SELECT * FROM AdventureWorks.dbo.customer adv INNER JOIN Northwind2008R2.dbo.customer nrt ON adv.CustomerName = nrt.CustomerName

D. SELECT * FROM AdventureWorks.dbo.customer adv INNER JOIN Northwind2008R2.dbo.customer nrt ON adv.CustomerName = nrt.CustomerName COLLATE SQL_Latin1_General_CP1_CS_AS;

 

Correct Answer: A

Explanation/Reference:

Disputed that this is the Correct Answer

SELECT *

FROM

AdventureWorks.dbo.customer adv INNER JOIN

Northwind2008R2.dbo.customer nrt ON adv.CustomerName = nrt.CustomerName COLLATE

SQL_Latin1_General_CP1_CS_AS;

 

Download Latest MCTS 70-432 Real Free Tests , help you to pass exam 100%.