Latest MCTS 70-432 Dumps Real Exam Download 131-140

Ensurepass

QUESTION 131

You work in a company which uses SQL Server 2008. You are the administrator of the company database.

Now you are in charge of a SQL Server 2000 instance.

You upgrade this instance to SQL Server 2008. You configure the Agent service to use the LocalSystem

account. There is a file named Orderdata on a remote network share. A job accesses this Orderdata file by

using a CMDExec step. The job step fails to complete execution after the upgrade.

You must make sure that the job can complete execution, so you have to configure the job step.

What action should you perform?

 

A. You should configure a certificate

B. You should configure the SQL Server Agent service to use the NetworkService account.

C. You should configure the job step to use a proxy account

D. You should configure the SQL Server Agent service to use a local Windows account

 

Correct Answer: C

Explanation/Reference:

Because job steps run in the context of a specific Microsoft Windows user, that user must have the

permissions and configuration necessary for the job step to execute.

For example, if you create a job that requires a drive letter or a Universal Naming Convention (UNC) path,

the job steps may run under your Windows user account while testing the tasks.

However, the Windows user for the job step must also have the necessary permissions, drive letter

configurations, or access to the required drive.

Otherwise, the job step fails. To prevent this problem, ensure that the proxy for each job step has the

necessary permissions for the task that the job step performs.

 

 

QUESTION 132

You work in a company which uses SQL Server 2008. You are the administrator of the company database.

Now you are in charge of a SQL Server 2008 instance.

A user reports that when he runs a query, it takes a quite long time and it is still unfinished. As the IT

support, you have to verify whether the query is blocked.

Which tool should you use?

 

A. You should use the Database Engine Tuning Advisor tool

B. You should use the Windows System Monitor tool

C. You should use the Job Activity Monitor tool in Microsoft SQL Server Management Studio

D. You should use the Activity Monitor tool in Microsoft SQL Server Management Studio

 

Correct Answer: D

Explanation/Reference:

The Activity Monitor in SQL Server Management Studio graphically displays information about:

Processes running on an instance of SQL Server.

Blocked processes.

Locks.

User activity.

This is useful for ad hoc views of current activity.

 

 

QUESTION 133

You work in a company which uses SQL Server 2008. You are the administrator of the company database.

Now you are in charge of a SQL Server 2008 instance.

You create a SQL Server Agent job that execute every 2 minutes. There is a text file named SQL.log on

the E: drive. The job logs information to the text file.

You get a report from users saying that the server doesn’t make response. You check and find that the

SQL Server Agent service does not run and the Agent job doesn’t function any longer.

As the IT support, you have to identify the reason for the failure of the SQL Server Agent service.

Which log should you check?

 

A. You should check ERRORLOG

B. You should check QL1.log

C. You should check SQLAGENT.OUT

D. You should check log_xx.trc

 

Correct Answer: C

Explanation/Reference:

The file SQLAGENT.OUT stores the SQL Agent running log. All the information about the latest run of the Agent is there. You can also find information into the event viewer.

 

 

QUESTION 134

You work in a company which is named Wiikigo Corp. The company uses SQL Server 2008. You are the

administrator of the company database.

Now you are in charge of a SQL Server 2008 instance. There is a database named DB1 in the instance.

You have set the recovery model of DB1 to Full. At 2:00 There is a full database backup of all the user databases every day. Every quarter the transaction log backup is performed. Every 4 hours a differential backup is occurred. You are going to perform a full backup of DB1 at 11:00. When you perform this back up, you must make sure that the overall backup will not be affected. What’s more, you have to restore procedures for DB1. You must make sure that the backup files are restored in proper sequence. In the options below, which Transact-SQL statement should you use?

 

A. BACKUP LOG DB1 TO DISK = ‘t:backupsdb1.trn’;

B. BACKUP DATABASE DB1 TO DISK = ‘t:backupsdb1.bak’ WITH DIFFERENTIAL;

C. BACKUP DATABASE DB1 TO DISK = ‘t:backupsdb1.bak’ WITH NOUNLOAD;

D. BACKUP DATABASE DB1 TO DISK = ‘t:backupsdb1.bak’ WITH COPY_ONLY;

 

Correct Answer: D

Explanation/Reference:

COPY_ONLY Specifies that the backup is a copy-only backup, which does not affect the normal sequence of backups. A copy-only backup is created independently of your regularly scheduled, conventional backups. A copy-only backup does not affect your overall backup and restore procedures for the database. Copy-only backups were introduced in SQL Server 2005 for use in situations in which a backup is taken for a special purpose, such as backing up the log before an online file restore. Typically, a copy-only log backup is used once and then deleted. When used with BACKUP DATABASE, the COPY_ONLY option creates a full backup that cannot serve as a differential base. The differential bitmap is not updated, and differential backups behave as if the copyonly backup does not exist. Subsequent differential backups use the most recent conventional full backup as their base.

Important:

If DIFFERENTIAL and COPY_ONLY are used together, COPY_ONLY is ignored, and a differential backup is created. When used with BACKUP LOG, the COPY_ONLY option creates a copy-only log backup, which does not truncate the transaction log. The copy-only log backup has no effect on the log chain, and other log backups behave as if the copy-only backup does not exist.

 

 

QUESTION 135

You work in a company which uses SQL Server 2008. You are the administrator of the company database.

Now you are in charge of a SQL Server 2008 instance.

Now you get an order from your company CIO, you have to identify whether a database integrity check

(DBCC CHECKDB) was run for a particular database.

Which log file should you examine?

 

A. log.trc

B. default.trc

C. ERRORLOG

D. SQLAGENT

 

Correct Answer: C

Explanation/Reference:

View the SQL Server error log to ensure that processes have completed successfully (for example, backup and restore operations, batch commands, or other scripts and processes). This can be helpful to detect any current or potential problem areas, including automatic recovery messages (particularly if an instance of SQL Server has been stopped and restarted), kernel messages, or other server-level error messages.

 

 

QUESTION 136

You work in a company which uses SQL Server 2008. You are the administrator of the company database.

Now you are in charge of a SQL Server 2008 instance on the same physical computer.

Now users report that they are unable to connect to the named instances. You check and verify that they

can only connect to the default instance. You also ensure that all SQL Server instances run normally.

You have to start the service which is required to connect to the named instances.

Which service should you start?

 

A. Server

B. SQL Server Agent

C. SQL Server Browser

D. SQL Active Directory Helper

 

Correct Answer: C

Explanation/Reference:

The SQL Server Browser program runs as a Windows service. SQL Server Browser listens for incoming requests for Microsoft SQL Server resources and provides information about SQL Server instances installed on the computer. SQL Server Browser contributes to the following actions: Browsing a list of available servers Connecting to the correct server instance Connecting to dedicated administrator connection (DAC) endpoints

 

 

QUESTION 137

You work in a company which uses SQL Server 2008. You are the administrator of the company database.

Now you are in charge of a SQL Server 2008 instance.

There are user-defined stored procedures. Now you have to make sure two things, first, the names of all

user-defined stored procedures must contain the prefix usp_ on all instances; second, stored procedures

that do not contain this prefix cannot be created by you.

So what should you do to ensure this two?

 

A. You should create a policy that targets the name of the stored procedure that is evaluated on change.

B. You should create a policy that targets the name of the stored procedure that is evaluated on demand.

C. You should create a condition that targets the name of stored procedure that is evaluated on demand.

D. You should create a condition that targets the name of the stored procedure that is evaluated on change.

 

Correct Answer: A

Explanation/Reference:

Policy-Based Management is a system for managing one or more instances of SQL Server 2008. When

SQL Server policy administrators use Policy-Based Management, they use SQL Server Management

Studio to create policies to manage entities on the server, such as the instance of SQL Server, databases,

or other SQL Server objects.

Policy-Based Management has three components:

Policy management

Policy administrators create policies.

Explicit administration

Administrators select one or more managed targets and explicitly check that the targets comply with a specific policy, or explicitly make the targets comply with a policy.

Evaluation modes

There are four evaluation modes, three of which can be automated:

On demand. This mode evaluates the policy when directly specified by the user.

On change: prevent. This automated mode uses DDL triggers to prevent policy violations.

Important:

If the nested triggers server configuration option is disabled, On change: prevent will not work

correctly. Policy-Based Management relies on DDL triggers to detect and roll back DDL operations that

do not comply with policies that use this evaluation mode. Removing the Policy-Based Management DDL

triggers or disabling nest triggers, will cause this evaluation mode to fail or perform unexpectedly.

On change: log only. This automated mode uses event notification to evaluate a policy when a relevant

change is made.

On schedule. This automated mode uses a SQL Server Agent job to periodically evaluate a policy.

When automated policies are not enabled, Policy-Based Management will not affect system performance.

 

 

QUESTION 138

You work in a company which uses SQL Server 2008. You are the administrator of the company database.

Now you are in charge of a SQL Server 2008 instance.

A new SQL Agent job has been created by you. The job includes a Windows PowerShell job step which

transfer data between servers by using the SQLCmd utility. If the job fails, you must make sure that an

operator named ReactGroup is informed by receiving an e-mail.

So what action should you perform to achieve this goal?

 

A. First you should enable Notifications. Direct the Notification to the ReactGroup operator on job failure after you create the ReactGroup operator.

B. First you should enable Notifications. Direct the Notification to the ReactGroup operator on failure of the Powershell job step after you create the ReactGroup operator.

C. First you should assign the ReactGroup operator as the failsafe operator. Then enable the job after you create the ReactGroup operator.

D. First you should configure the job step proxy account to use the ReactGroup operator account after you create the ReactGroup operator. At last choose the proxy account for the e-mail profile.

 

Correct Answer: A

Explanation/Reference:

In this case, you should add send job status to the operator via the following procedure:

This topic describes how to set notification options so Microsoft SQL Server Agent can send notifications

to operators about jobs.

To notify an operator of job status

1.

 In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that

instance.

2.

 Expand SQL Server Agent, expand Jobs, right-click the job you want to edit, and select Properties.

3.

 In the Job Properties dialog box, select the Notifications page.

4.

 If you want to notify an operator by e-mail, check E-mail, select an operator from the list, and then

select one of the following:

When the job succeeds to notify the operator when the job completes successfully.

When the job fails to notify the operator when the job completes unsuccessfully.

When the job completes to notify the operator regardless of completion status.

5.

 If you want to notify an operator by pager, check Page, select an operator from the list, and then select

one of the following:

When the job succeeds to notify the operator when the job completes successfully.

When the job fails to notify the operator when the job completes unsuccessfully.

When the job completes to notify the operator regardless of completion status.

6.

 If you want to notify an operator by net send, check Net send, select an operator from the list, and then

select one of the following:

When the job succeeds to notify the operator when the job completes successfully.

When the job fails to notify the operator when the job completes unsuccessfully.

When the job completes to notify the operator regardless of completion status.

 

 

QUESTION 139

You work in a company which uses SQL Server 2008. You are the administrator of the company database.

Now you are in charge of a SQL Server 2008 instance.

There is a stored procedure. The stored procedure implements a database maintenance process.

A SQL Server Agent job should be created to that runs the stored procedure. Besides this, you must make

sure that after it is completed successfully, the job is removed.

What should you do?

 

A. You should create a job which is assigned to the Database Maintenance category

B. You should create an Alert which will run another to delete the maintenance job.

C. You should create a job. You schedule the job to run only once.

D. You should create a job. The job uses the Automatically delete job option

 

Correct Answer: D

Explanation/Reference:

This topic describes how to configure Microsoft SQL Server Agent to automatically delete jobs when they

succeed, fail, or complete.

To automatically delete a job

In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that

instance.

Expand SQL Server Agent, expand Jobs, right-click the job you want to edit, and then click Properties.

Select the Notifications page.

Check Automatically delete job, and choose one of the following:

Click When the job succeeds to delete the job status when it has completed successfully.

Click When the job fails to delete the job when it has completed unsuccessfully.

Click When the job completes to delete the job regardless of completion status.

 

 

QUESTION 140

You work in a company which uses SQL Server 2008. You are the administrator of the company

database.

Now you are in charge of a SQL Server 2008 instance which contains multiple Agent jobs. The jobs run

each job on each day of the week by using seven shared schedules.

You must make sure that a job named Job4 doesn’t run Wednesday.

So what action should you perform to achieve this goal?

 

A. You should remove the schedule for Wednesday from Job4.

B. You should add a new Job4 schedule for Wednesday and disable it.

C. You should delete the schedule for Wednesday.

D. You should disable the schedule for Wednesday.

 

Correct Answer: A

Explanation/Reference:

SQL Server Agent Job runs at determined time via the Schedules that are in the “Schedules” tab. Here we have one schedule for each days of the week (7 schedules in total). So, to pevent the job to run on Wednesday, we have to remove this schedule.

 

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