Latest MCTS 70-432 Dumps Real Exam Download 71-80

Ensurepass

QUESTION 71

You are the administrator in charge of two instances both of which run on the same computer. One is a

SQL Server 2008 instance; another is a SQL Server 2005 instance.

There is a database named DB1 in the SQL Server 2008 instance. DB1 uses the Fulltext indexes. Several records that include the word “root” are added to DB1. An empty resultset is returned when the Fulltext index is queried for the word “root.”

You have to make sure the query can return records that contain the word “root”.

What should you do?

 

A. You should terminate and restart the MSFTESQL service.

B. You should rebuild the full-text index.

C. The word “root” should be added to the stop list.

D. The word “root” should be added to the thesaurus file.

 

Correct Answer: B

Explanation/Reference:

 

 

QUESTION 72

You are the administrator of a remote SQL Server 2008 instance.

You notice that the application has a poor performance. On the remote SQL Server instance, you capture a workload of the remote instance to a trace table by using SQL Profiler.

You have to use the Database Engine Tuning Advisor to analyze the workload of the remote SQL Server instance on a local SQL Server instance.

Which action should you perform to achieve this goal?

 

A. You should enable the XP_MSVER stored procedure on the local server.

B. You should recapture the workload by using the data collector.

C. You should enable the XP_MSVER stored procedure on the remote server.

D. You should recapture the workload to a trace file by using SQL Profiler.

 

Correct Answer: D

Explanation/Reference:

You should recapture the workload to a trace file by using SQL Profiler. by Tiamin:

In order to use the Database Engine Tuning Advisor, you need one trace file, here in the question, there is a trace table available (on the remote server), which is not good (on the local server). We have then to restart one trace to a file (on the local server).

 

 

QUESTION 73

You are the administrator of a SQL Server 2008 instance with a database named DB1.

DB1 has two filegroups: one is a filegroup named History that contains historical data stored in the G: volume; the other is the PRIMARY filegroup that contains the On-Line Transaction Processing (OLTP) data stored in the F: volume.

The backup strategy of DB1 has three requirements:

The History filegroup should be backed up at the end of each working day;

The PRIMARY filegroup should be backed up five times a day;

The transaction log should be backed up every quarter.

You get a report at 13:00 saying that the volume which contains the History filegroup fails.

As the technical support, you have to restore the database, and you must reduce the data loss to the least as soon as possible.

What should you do?

 

A. You should restore the most recent backup of the History filegroup.

B. You should restore the most recent backup of the History filegroup. Restore all the transaction log backups after the most recent backup of the History filegroup

C. You should backup the transaction log. Then restore the most recent backup of the PRIMARY filegroup and restore the most recent backup of the History filegroup. After the most recent backup of the History filegroup, restore all the transaction log backups

D. You should backup the transaction log. Then restore the most recent backup of the History filegroup and restore all the transaction log backups after the most recent backup of the History filegroup.

 

Correct Answer: D

Explanation/Reference:

 

 

QUESTION 74

You are the administrator of a SQL Server 2008 instance with an On-Line Analytical Processing (OLAP) database.

The database contains a dimension table named Clients. Ever hour backup of data of the Clients table is performed. But the Clients table contains redundant data.

You have to keep the disk space used to store the Clients table.

Which compression technology should you use?

 

A. You should use backup compression

B. You should use row compression

C. You should use page compression

D. You should use windows NTFS file system compression

 

Correct Answer: C

Explanation/Reference:

Explanation:

Page Compression

This is a most vital compression method and is useful when there is a lot of frequently occurring data.

Page compression allows common data to be shared between rows for a given page. Its uses the following techniques to compress data:

Row compression.

The Row Compression feature applies to zeros and null values and optimizes their space in SQL Server.

In fact, due to the Row Compression feature, SQL Server does not take any disk space for zero or null values. For, all numeric datatypes (decimal, datetime, money, int etc), if they are storing zero or null values in their rows, the Row Compression feature does not store them in the database at all. To apply row level compression the whole table needs to be REBUILDt with the Data_Compression = ROW clause enabled.

The following command is used to rebuild a row compressed table:

ALTER TABLE <Table.Name> REBUILD

WITH (DATA_COMPRESSION = ROW)

Prefix Compression.

For every column in a page duplicate prefixes are identified. These prefixes are saved in compression information headers (CI) which resides after the page header. A reference number is assigned to these prefixes and that reference number is replaced where ever those prefixes are being used.

Dictionary Compression.

Dictionary compression searches for duplicate values throughout the page and stores them in CI. The main difference between prefix and dictionary compression is that ‘prefix’ is only restricted to one column while dictionary is applicable to the complete page.

Because of the Prefix Compression and Dictionary Compression, duplicate values are shared between rows in a given page… Which lead us to reduced disk space used.

Read the following about Considerations for When You Use Row and Page Compression:

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

 

 

QUESTION 75

You are the administrator of a SQL Server 2008 instance.

A SQL Server Agent job is failing. You find that the job history information is incomplete and seems to be truncated after you review it.

All information produced by a job must be available for viewing.

Which action should you perform to achieve this goal?

 

A. You should enable notifications to the Windows application event log when the job completes.

B. You should enable all job steps, making them send the output to a file

C. You should enable write OEM file

D. You should include execution trace messages in the SQL Agent Error log

 

Correct Answer: B

Explanation/Reference:

Ans: You should enable all job steps, making them send the output to a file

Feedback:

The output file sets the file to use as the output from each job step.

For the job steps, in Management Studio, go to the option panel and select Output file to get all the info from the step. Note that you can append to or override the file; all job steps should be adapted with the option.

 

 

QUESTION 76

You are the administrator of a SQL Server 2008 instance.

There is a text file which is named SQL01.log on the F: drive. A SQL Server Agent job executes every 2 minutes and logs information to the text file.

You get a report from users saying that the sever does not respond.

As the technical support, you check and find that the SQL Server Agent service is not running and the SQL

Server Agent job no longer functions.

You have to identify the reason why the SQL Server Agent service is unresponsive.

Which log should you check?

 

A. SQL1.log

B. SQLAGENT.OUT

C. log_xx.trc

D. ERRORLOG

 

Correct Answer: B

Explanation/Reference:

You should check SQLAGENT.OUT log

Explanation:

All the information about SQL Server Agent errors can be found in the SQLAGENT.OUT file. The default in my case is C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLLog

SQLAGENT.OUT.

The primary interface to access the SQL Server Error Logs is via the Log File Viewer.

How do you access the Log File Viewer?

Launch SQL Server Management Studio.

Navigate to your SQL Server 2008 instance.

Expand the instance, then expand the Management folder followed by the SQL Server Logs folder.

Double click on any of the log files.

More info: http://www.mssqltips.com/sqlservertip/1021/sql-server-2005-log-file-viewer/

 

 

QUESTION 77

You are the administrator of a SQL Server 2008 instance.

A user named Mary reports that she is waiting for a query to complete.

You need to ascertain whether the query is blocked.

Which tool should you use?

 

A. The Windows System Monitor tool

B. The Database Engine Tuning Advisor tool

C. The Activity Monitor tool in Microsoft SQL Server Management Studio D. The Job Activity Monitor tool in Microsoft SQL Server Management Studio

 

Correct Answer: C

Explanation/Reference: Use Activity Monitor to obtain information about SQL Server processes and how these processes affect the current instance of SQL Server. Activity Monitor is a tabbed document window that has the following expandable and collapsible panes: Overview, Active User Tasks, Resource Waits, Data File I/O, and Recent Expensive Queries. When any pane is expanded, Activity Monitor is querying the instance for information. When a pane is collapsed, all querying activity stops for that pane. You can also expand one or more panes at the same time to view different kinds of activity on the instance. http://msdn.microsoft.com/en-us/library/cc879320(v=sql.105).aspx You can use the Job Activity Monitor in SQL Server Agent to view the current state of jobs. If the SQL Server Agent service unexpectedly terminates, you can refer to the sysjobactivity table to see which jobs were being executed when the service terminated. http://msdn.microsoft.com/en-us/library/ms187449.aspx

 

 

QUESTION 78

You administer a Microsoft SQL Server 2008 R2 database.

clip_image002

You discover that the Windows application log is being filled with entries from a SQL Server Audit process named DatabaseAudit. The volume of these events is causing older events to be removed from the log.

You need to ensure that the following requirements are met:

SQL Server Audit information is stored in the

D:AuditLogs folder.

No data is currently lost in the Windows application log.

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_image004 

Correct Answer:clip_image006

Explanation/Reference:

CORRECT STEPS:

1. Create a folder named AuditLogs on drive D.

2. Open Microsoft SQL Server Management Studio and connect to the server.

3. Browse to the SecurityAudits folder and double-click the DatabaseAudit object.

4. Change the Audit Destination to File and specify D:AuditLogs as the file path.

 

 

QUESTION 79

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 computer which hosts several applications in your company.

The instance runs on the computer. A job named DeliveryList is created by you. This job requires a file to be written to a file server.

But because the job cannot access the file server, it fails to run. You intend to configure the SQL Server

Agent service so that only the SQL Server Agent service has read and write access to the file server.

Which account type should you use?

 

A. You should use local Service account

B. You should use network Service account

C. You should use domain account

D. You should use local System account

 

Correct Answer: C

Explanation/Reference:

In order to access file shares, the account that runs the job should have access rights to the remote object.

In this case, the user that runs the job is “sa”. As it is “sa”, the real user that runs the job is SQL Server

Agent user.

So, use one domain account for the SQL Server Agent and grant the correct rights to this user on the file server.

 

 

QUESTION 80

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.

On DB1, quite few logged operations are performed.

Now according to the requirement of the company CIO, you must validate that the database can be restored to a specific point in time.

So what action should you perform to achieve this goal?

 

A. You should verify that the simple recovery model is used by the database

B. You should verify that the full recovery model is used by the database.

C. You should verify that the checksum page verify option is used by the database

D. You should verify that the bulk-logged recovery model is used by the database

 

Correct Answer: B

Explanation/Reference:Full recovery model

Provides the normal database maintenance model for databases where durability of transactions is necessary. Log backups are required. This model fully logs all transactions and retains the transaction log records until after they are backed up. The full recovery model allows a database to be recovered to the pointof failure, assuming that the tail of the log can be backed up after the failure. The full recovery model also supports restoring individual data pages.

 

 

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